Showing posts with label Entity Framework. Show all posts
Showing posts with label Entity Framework. Show all posts

Tuesday, 10 January 2017

EF lazy and Eager loading–caught out! (Putting lazy load back on)

I was a bit optimistic in my last post on EF.  Turing off Lazy Loading (removing virtual) on a attribute does NOT imply eager loading.  Documentation is unlcear but confirmed by internet -

“IMPORTANT: You could easily think that, once you disable Lazy Loading, the framework will auto-load each and every related property: it won’t.” - http://www.ryadel.com/en/enable-or-disable-lazyloading-in-entity-framework/

So I’m back to the .Include on each of my get methods to ensure consistency.

Not sure the argument “Don’t worry, it’s a good thing! You don’t want your DB to be automatically wasted on each Entity query request.” isn’t a bit of a cop out.  I want to decide which of my attributes are composite – think car and wheels – and load those all the time.

Of course – I can do this with the .Include but its a bit less explicit.  Making the attribute virtual again will at least means the serialisation falls over if I forgot to include the Lazy Load/Serialisation fails as it’s outside the context – so that at least enforces my aggregate.

Sunday, 8 January 2017

On EF lazy loading and UML, EF and Serialisation

 

On EF and lazy loading and UML

A good few years ago (back in 2010) I looked at EF and one of my concerns was that you could not control the loading.  A had an object which had a collection of objects – like a car and wheels, and when I fetched the car the framework would not fetch the wheels at the same time – it went and got them one at a time.

This did not appear to be a scalable solution.

This time round things seem a lot better and I have been able to use the techniques here - https://msdn.microsoft.com/en-us/library/jj574232(v=vs.113).aspx – to control what gets loaded when.  Seems pretty neat.

I am fairly impressed with this as it allows me to decide what is always “Eagerly” loaded in the class model – what in OO days would have been an composite in my UML model and in the LINQ stuff optionally load the association.

On EF, Normalisation and Serialisation

I am using the EF (code first) objects as my “Business Objects”.  Not sure this has lead to massive time savings over raw ADO.NET as all that annotation is quite a pain.  Yes I could just generate the code (I started like that) but I use some pretty old school naming conventions on my database – Hungarian notation – because I’m old – so attribute names become compromised.

One of my other concerns with EF has been security but I’m assured that LINQ for EF will generate parameterised queries which are good (for security) and meet OWSA

So far I’m fairly happy that my object layer is not too normalised – but I’ve yet to tackle anything too serious – like inheritance and a class comprised of two tables.  Not sure about the former yet but a view will probably help the later.

A couple of things – if you are serialising (to JSON in my case for the service layer) then you need to decide how much of the tree you are bringing back and what you will serialise.

  [Table("tblEvent")]
    [DataContract]
    public partial class Event
    {
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
        public Event()
        {
            tblEventParticipants = new HashSet<tblEventParticipant>();
        }

        [Key]
        [Column("intEventId")]
        [DataMember]
        public int EventId { get; set; }

        [Column("dtePlannedDate")]
        [DataMember()]
        public DateTime PlannedDate { get; set; }

       [Column("dteActualDate ")]
       [DataMember()]
        public DateTime? ActualDate { get; set; }

        [Column("intOrganiserId")]
        [ForeignKey("Organiser")]
        [DataMember()]
        public int? OrganiserId { get; set; }

        [Column("intRouteId")]
        [DataMember()]
        [ForeignKey("Route")]
        public int RouteId { get; set; }

        [DataMember()]
        public tblOrganiser Organiser { get; set; }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
        public virtual ICollection<tblEventParticipant> tblEventParticipants { get; set; }

       
        [DataMember()]
        public Route Route { get; set; }
    }

So I’m marking my classes with the  DataContract attribute, marking the members to be serialised with DataMember attribute and making sure they are always loaded by removing the virtual keyword (Eagerly loading) else the serialisation will fail.

One hurdle I’ve yet to cross is that I can see times when I may wish to load and serialise some of the associations – will need to work this through.

Wednesday, 2 November 2016

The EF Conversation with @shaydmusic

Pretty sure twitter will give you the complete story

Sunday, 30 October 2016

Can I Sprint in an hour?

ACP Brevet Cards

It seems the more I know, the less I get to do.  I'm currently a technical architect for 6 sprint teams - which is exciting and challenging but I'm getting less and less (read no) time at the coal face.
The pace of change in IT seems to be exponential and I like to keep my toes on the edge of the programming curve.
OK - so I'd love to get back into Unix, c++, java and all that stuff - but it's been 15 years or so so I'm going to stick with the Microsoft stack.

The Challenge

Deliver a user story in one hour.  That's one hour to code, test and release.  
"Give me six hours to chop down a tree and I will spend the first four sharpening the axe." 
Abraham Lincoln

I am going to allow - outside of the hour - study and plan.  Hopefully this will make the hour super productive.

The project

The project is simple - and combines programming with my other passion - cycling.  You can read about some of my cycling here.  Every year I organise a few "Brevets" - long distance cycle rides for Audax Ireland.  There is a bit of admin involved:

  1. Collect details of participants - currently they email or post a form with name, address etc.
  2. Collect money - I accept PayPal or cash on the day.  Not all organisers accept cash and some push the PayPal through the clubs.  Different events cost different amounts.  Payment can be made in € or £.
  3. Print "Brevet Cards" with the event details, riders details and control points.
  4. Sign on sheet for Cycling Ireland (CI) with list of riders to collect signature at start.
  5. One day licence form for riders who aren't members of cycling Ireland.
  6. On completion - fill in finisher details on spreadsheet for Audax Club Parisien to homogolate the rides and order medals where required.
  7. Submit accounts and pay balance toe Audax Ireland (AI).
  8. When homogolation stickers and medals arrive, post with brevet cards to riders.
Currently I run all this using a spreadsheet to collect the rider info on.  I then have a word mail merge for the Brevet cards and cut and paste onto the other documents.
The biggest pain is actually getting the data on the spreadsheet.  It is either keyed or cut an pasted depending on how the application arrives.  I can also sometimes cut and paste from an old spreadsheet - but the CI license numbers change every year and other details may change.

The tech

  • SQL Azure - assuming a relational database - might need other azure tech around the outside for large data or whatever.
  • C#.net for business object layer, processes etc.
  • MVC for serving HTML  to client
  • WEB API - for services
  • Angular
  • Visual Studio

Entity framework or ADO.NET + Strongly typed stored procedures.

Its a  question that's been worrying me for about 8 years!  
Currently I have projects working with ADO.NET and stored procs (most), EF and unit of work, and EF over stored procedures.  I'm still torn between development speed, security, maintanence and 

Strongly Typed Stored Procedures

  • Tick a big security box - giving lease privilege access to the database and strong type checking - a string is a string never a piece of sql, thus adding a layer of defence against many forms of attack, SQL injection being the most obvious.  OK - O know EF will have vendor backing and is paramaterised etc. etc. but believe me, when some external auditor comes in and says what layers of defence do you have against attack, the strongly typed stored procedure with least privilege (i.e. connection can only execute stored procs) is king.
  •  Are fast
  • Do set based operations where they belong - in an engine that has been optimised over about twenty five years for performing set based operations!
  • Allow provide an encapsulation layer over your data model, allowing it to be changed and maintained independent(ish) of your code.
  • Allow your database to be normalised and your object model to be de-normalised as. it should be.
  • Allow your interactions with the database to be as they should i.e. full control over how much data is fetched when.
  • Is stable with a very long support life ahead and behond - ADO.NET.
  • Can lead to business logic being scattered across code and sql depending on who wrote it!
  • Can result in stored procedure mountains.
  • Offer a great maintainence fix.

Entity Framework

  • Is quicker to develop - though I'd argue this might only be over the first few iterations of a project.
  • Supports more flexible query functionality for the application e.g. sorting, querying multiple parameters etc. without additional effort.
  • Can leave you fighting the framework.
  • Is not as stable
  • Is not as secure
  • Through web services - offers a consistent business layer for other applications e.g. reporting etc.
  • May lead to compromises in the data model (de-normalised) or the business object model (too normalised).
  • Requires a full code deploy.
I'm going to put this decision off a bit longer!

Project management and tools

OK - so its agile, obviously.  

Agile tool/work tracking

TBD - I have visual studio online/visual studio team system and this is the obvious choice but I might look at a more "open" product - after all I'm wearing shorts right!
I guess initially it'll be this blog as my Minimum Viable Product in an hour is going to be very minimum.

Source Code Repository

To open source or not to open source, that is the question - well - I really want to open source - not that I'm expecting a million IT pros to rock up and start working on this but, like I say, I wearing shorts.  I thing I need to be careful of is ideally I'd use some of Stiona Software's utility and platform code but this has IPR wrapped in it and I'm not wearing sandals.  Software is my living too.

Again - with my shorts on GIT - but frankly it's something else to learn at the moment.

So - probably codeplex.  I have an account and it integrates with visual studio.

Tuesday, 19 October 2010

Experimenting with MVC and the Entity Framework – Part 4 - Authentication and Authorisation

Authentication and authorisation.

.Authentication = Who is it.

Authorisation = can they do it.

So – reading through the NerDinner section on authorisation and authentication then it looks like I can use good old FORMSAuth and APSNETDB to provide authentication and authorisation store out of the box.

I’ve been using these for a good few years now so don’t expect any issues.

I may change this later to:

o use a STS (Security Token Service) such as the excellent thinktecture Starter STS so I can secure my web services to a WS standard using the same backend store.

o Allow authentication from other systems such as facebook, google etc.

[more]

So I change my web.config to give a different name to the database as I know in my production database I have a few of these already! (Actually the database does support multiple applications but its much more difficult to migrate and maintain applications if they all use the same database).

<add name="ApplicationServices" connectionString="data source=.\SQL2008EXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb_victor.mdf;User Instance=true" providerName="System.Data.SqlClient" />

Ran the app and went to the register page.

clip_image002[9]

Database was created for me – worked a treat!

clip_image004[10]As you can see it now knows who I am!

Logged off and tried to add a comment – as expected it took me to the login screen. I had forgotten my password already so I was registered again.

I was not taken to the add comment screen – where I was originally heading.

I could not get my password back!

I’ll tackle the password retrieval first.

Password retrieval

Change the web.config to force unique emails and allow password reset:

<add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" connectionStringName="ApplicationServices" enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="true" passwordFormat="Hashed" maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10" passwordStrengthRegularExpression="" applicationName="/" />

And then I added a class into the existing account model page:

public class PasswordRetrieveModel

{

[Required]

[DisplayName("User name or Email address")]

public string UserName { get; set; }

}

This class models the attributes required to retrieve our password.

I then added in a strongly typed view to show the details:

<%@ Page Title="" Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage<victor.Models.PasswordRetrieveModel>" %>

<asp:Content ID="Content1" ContentPlaceHolderID="TitleContent" runat="server">

RetrievePassword

</asp:Content>

<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">

<h2>RetrievePassword</h2>

<% using (Html.BeginForm()) { %>

<%= Html.ValidationSummary(true,"The password could not be reset") %>

<fieldset>

<legend>Fields</legend>

<div class="editor-label">

<%= Html.LabelFor(m => m.UserName) %>

</div>

<div class="editor-field">

<%= Html.TextBoxFor(m => m.UserName) %>

<%= Html.ValidationMessageFor(m => m.UserName) %>

</div>

</fieldset>

<p>

<input type="submit" value="RetrievePassword" />

</p>

<% } %>

</asp:Content>

and edited the model adding in password reset methods on the IMembershipService interface:

/// <summary>

/// reset the password

/// </summary>

/// <param name="p">username or email address</param>

/// <returns>flag indicating if password sent to the user.</returns>

bool ResetPassword(string p);

And an implementation on the AccountMembershipService class:

public class AccountMembershipService : IMembershipService

{

private static String rawMessage = @"You have requested a password reset for {0}.

Your username is: {1}

Your new password is: {2} ";

public bool ResetPassword(String p)

{

bool ret = false;

// first find the user by username or email address.

String username = null;

MembershipUser user = _provider.GetUser(p, true);

if (null == user)

{

username = _provider.GetUserNameByEmail(p);

if (null != username)

{

user = _provider.GetUser(username, true);

}

}

else

{

username = user.UserName;

}

// if we've found a user then send out his (reset) password

if (null != username)

{

user.UnlockUser();

String newpass = _provider.ResetPassword(username, null);

SmtpClient mailClient = new SmtpClient();

MailAddress address = new MailAddress(user.Email);

String message = String.Format(rawMessage,

HttpContext.Current.Request.Url.Host.ToString(),

user.UserName,

newpass );

mailClient.Send(ConfigurationSettings.AppSettings["fromemail"],

user.Email,

"Password Reset",

message);

ret = true;

}

return ret;

}

}

Finally I added new methods into the account controller:

// *****************************************

// URL: Password Request

// *****************************************

public ActionResult RetrievePassword()

{

return View();

}

[HttpPost]

public ActionResult RetrievePassword(PasswordRetrieveModel model)

{

ActionResult ret = null;

if (MembershipService.ResetPassword(model.UserName))

{

return View("PasswordRetrieved");

}

else

{

ModelState.AddModelError("", "The user was not found using the given Username or EMail address.");

ret = View();

}

return ret;

}

And added a section in the web.config to configure the smtp service.

<system.net>

<mailSettings>

<smtp deliveryMethod="Network">

<network host="localhost"/>

</smtp>

</mailSettings>

I usually set my local smtp service to allow relaying and relay to my real smtp server or just examine the mail in the iis dirctories where its droped (C:\Inetpub\mailroot\Queue ) to test. That way when I deploy to production (where the relaying works) I don’t need to change anything.

The relaying properties can be found in the Access tab of the smtp server properties in inetmgr.

clip_image006[8]

clip_image008[8]

Redirecting after registration

First thing to do is to pass the url to the registration page as part of the query string.

Please enter your username and password. <%= Html.ActionLink("Register", "Register", new { ReturnURL = Request.QueryString["ReturnURL"] })%> if you don't have an account.

I have to say at this point I have no idea how this works! Specifically the new { ReturnURL = Request.QueryString["ReturnURL"] } – how is the ReturnURL type declared?

The help says

“routeValues

Type: System..::.Object
An object that contains the parameters for a route. The parameters are retrieved through reflection by examining the properties of the object. The object is typically created by using object initializer syntax.

I read up on object initializer syntax on msdn but still don’t understand how the type of ReturnURL is not being delcared. Maybe the compiler is now smart eneugh to work out the type?

However – it does appear in the query string and so I can ammend the register method to take and act on this paramater–

[HttpPost]

public ActionResult Register(RegisterModel model, string returnUrl)

{

if (ModelState.IsValid)

{

// Attempt to register the user

MembershipCreateStatus createStatus = MembershipService.CreateUser(model.UserName, model.Password, model.Email);

if (createStatus == MembershipCreateStatus.Success)

{

FormsService.SignIn(model.UserName, false /* createPersistentCookie */);

if (!String.IsNullOrEmpty(returnUrl))

{

return Redirect(returnUrl);

}

else

{

return RedirectToAction("Index", "Home");

}

}

else

{

ModelState.AddModelError("", AccountValidation.ErrorCodeToString(createStatus));

}

}

OK – pretty much all core functionality is there now. I just need to add venue matching – so we don’t duplicate venues, tidy up the screens with some help and put them in the right places, then, hopefully, I’ll have something to put live!

Experimenting with MVC and the Entity Framework – Part 3 – Creating the comments.

This follows on from part 2.

So now to add in the creation of comments. Having read through the CRUD implementation in Nerddinner and using partials again, I decided to go straight for a partial for my comments creation, as I would almost certainly require an edit and maybe a view details too.

[more]

clip_image002

So I first created a partial using the add view option and then added a create page – again using the add view.

clip_image004

But this time the view was not strongly typed and empty.

I then set the Create View to display the partial

<%@ Page Title="" Language="C#" MasterPageFile="~/Views/Shared/Site.Master" Inherits="System.Web.Mvc.ViewPage" %>

<asp:Content ID="Content1" ContentPlaceHolderID="TitleContent" runat="server">

Create

</asp:Content>

<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="server">

<h2>Create</h2>

<% Html.RenderPartial("CommentForm"); %>

</asp:Content>

A first bash at the post.

I new this wouldn’t work due to the FC constraint on the comments table requiring a venue – but I wanted to see what would happen:

[HttpPost]

public ActionResult Create(FormCollection collection)

{

Comment comment = new Comment();

try

{

UpdateModel<Comment>(comment);

victorEntities ve = new victorEntities();

ve.AddToCommentSet(comment);

ve.SaveChanges();

return RedirectToAction("Index");

}

catch

{

return View();

}

}

As expected – it blew up but due to the way this is coded – ie the catch “swallows” all exceptions – no error is presented to the user!

In my opinion this is an abuse of exceptions. Exceptions should not be used for normal program flow as:

1) When you are calling a method you have no way of determining what exceptions it throws/you need to handle (unlike java where you must explicitly declare the exceptions thrown – althogh this is not great as it forces the calling code to explicitly handle those exceptions in some way even if they can’t or declare that they throw them).

2) Exceptions should be just that – Exceptions. Business logic should be explicitly handled by code. In this case lack of input is definitely expeced. Exceptions should be for things beyond your control such as a file dissapearing or running out of memory.

My preference is always to handle exceptions in a global manner (usualy in the global.asax) – basically displaying something nice to the user and logging all the details you can. In theory we should never get any but they may happen due to

· Bugs

· Hardware problems such as networking errors.

· Scaleability issuses

You can then monitor these in test and production and take necesasry.

I will add error logging at a later date.

So I re-engineered the method to

[HttpPost]

public ActionResult Create(FormCollection collection)

{

ActionResult ret = View();

Comment comment = new Comment();

if(TryUpdateModel<Comment>(comment))

{

victorEntities ve = new victorEntities();

ve.AddToCommentSet(comment);

ve.SaveChanges();

ret = RedirectToAction("Index");

}

return ret;

}

Notice – I also added in a single exit point – makes the code much easier to read and helps reduce resources left open (such as files etc.).

Adding in the venue.

A comment is not valid without a venue which must also have a type.

The Nerd Dinner sample does not cover aggregates – they never do – so I thought I’d just have a stab at it anyway.

Firstly I created an empty object in the GET

//

// GET: /Comments/Create

public ActionResult Create()

{

Comment ncomment = new Comment();

Venue venue = new Venue();

ncomment.Venue = venue;

return View(ncomment);

}

And then following the instructions on the ObjectContext.AddObject Method I changed the POST method to add the venue first to the entities first:

//

// POST: /Comments/Create

[HttpPost]

public ActionResult Create(FormCollection collection)

{

ActionResult ret = View();

Comment comment = new Comment();

if(TryUpdateModel<Comment>(comment))

{

victorEntities ve = new victorEntities();

ve.AddToVenueSet(comment.Venue );

ve.AddToCommentSet(comment);

ve.SaveChanges();

ret = RedirectToAction("Index");

}

return ret;

}

Finally I added in the html on the form for the venue name to test it.

<div class="editor-label">

<%= Html.LabelFor(model => model.Venue.vcrName) %>

</div>

<div class="editor-field">

<%= Html.TextBoxFor(model => model.Venue.vcrName) %>

<%= Html.ValidationMessageFor(model => model.Venue.vcrName)%>

</div>

I checked in the debugger and the name was correctly set to what was entered. An exception was thrown that the foreign key constraint to VenueType was broken – which was expected.

Adding a venue type dropdown.

clip_image006

Again – I looked through the NerdDinner sample and decided that in MVC speak I needed to either use ViewData or a view model.

ViewData involves creating a SelectList in the controller –

[Authorize]

public ActionResult Edit(int id) {

    Dinner dinner = dinnerRepository.GetDinner(id);

    ViewData["Countries"] = new SelectList(PhoneValidator.AllCountries, dinner.Country);

    return View(dinner);

}

and displaying it in the View.

<%= Html.DropDownList("Country", ViewData["Countries"] as SelectList) %>

A view model involves creating a new Class based on the items you want to display.

The ViewData appears to be more of a “quick and dirty” solution that is not type safe and requires the setting of the view data in multiple places – e.g. both the get and the post.

So I opted to do the right thing – a philosophy that I’ve found has always worked well!

Whilst doing this I learn’t something new. Visual Studio 2005 featured a code snippet for properties which was fantastic. However, it always seemed broken in 2008. Since I had a bit of time I decided to try and get it back only to discover I no longer needed to declare my class attribute as it would be automatically generated. If I wanted my getter or setter to do something I can always change the code to a specific “backing” store (such as an attribute) later. This is called “Automatic Properties” – this guy was also looking for his code snippet but this lead me to this nice blog on automatic properties.

Here’s what I did to get it all working –

1) Created a new model class specific to the data I was modelling.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

namespace victor.Models

{

public class CommentFormViewModel

{

/// <summary>

/// The venue types to display.

/// </summary>

public SelectList VenueTypes { get; set; }

/// <summary>

/// The view comment

/// </summary>

public Comment ViewComment { get; set; }

public CommentFormViewModel(Comment comment)

{

victorEntities ve = new victorEntities();

ViewComment = comment;

VenueTypes = new SelectList(ve.VenueTypeSet.ToList<VenueType>(),

"intVenueTypeId",

"vcrName",

comment.Venue.Type);

}

public CommentFormViewModel()

{

}

}

}

2) Changed the View Form to display extra attributes and inherit from the new model class:

<%@ Control Language="C#" Inherits="System.Web.Mvc.ViewUserControl<victor.Models.CommentFormViewModel>" %>

<% using (Html.BeginForm()) {%>

<%= Html.ValidationSummary(true) %>

<fieldset>

<legend>Fields</legend>

<div class="editor-label">

<%= Html.LabelFor(model => model.ViewComment.Venue.Type.intVenueTypeId ) %>

</div>

<div class="editor-field">

<%= Html.DropDownListFor(model => model.ViewComment.Venue.Type.intVenueTypeId ,Model.VenueTypes ) %>

<%=Html.ValidationMessageFor(model => model.ViewComment.Venue.Type)%>

</div>

<div class="editor-label">

<%= Html.LabelFor(model => model.ViewComment.Venue.vcrName) %>

</div>

<div class="editor-field">

<%= Html.TextBoxFor(model => model.ViewComment.Venue.vcrName)%>

<%= Html.ValidationMessageFor(model => model.ViewComment.Venue.vcrName)%>

</div>

3) Updated my create model to return the new Model

// GET: /Comments/Create

public ActionResult Create()

{

Comment ncomment = new Comment();

Venue venue = new Venue();

ncomment.Venue = venue;

CommentFormViewModel model = new CommentFormViewModel(ncomment);

return View(model);

}

4) Updated my create post method to use the new model, set the venue type based on the ID and complete some other mandatory fields to make the whole thing work.

// POST: /Comments/Create

[HttpPost]

public ActionResult Create(FormCollection collection)

{

ActionResult ret = View();

CommentFormViewModel fvm = new CommentFormViewModel();

if(TryUpdateModel<CommentFormViewModel>(fvm))

{

victorEntities ve = new victorEntities();

fvm.ViewComment.dteCreated = DateTime.Now;

fvm.ViewComment.dteLastUpdated = DateTime.Now;

fvm.ViewComment.vcrUserUpdated = HttpContext.User.Identity.Name;

fvm.ViewComment.Venue.dteCreated = DateTime.Now;

fvm.ViewComment.Venue.dteLastUpdated = DateTime.Now;

fvm.ViewComment.Venue.vcrUserUpdated = HttpContext.User.Identity.Name;

// set the venue type to an existing one

fvm.ViewComment.Venue.Type = ve.VenueTypeGetByID(

fvm.ViewComment.Venue.Type.intVenueTypeId).ElementAt<VenueType>(0);

ve.AddToVenueSet(fvm.ViewComment.Venue );

ve.AddToCommentSet(fvm.ViewComment);

ve.SaveChanges();

ret = RedirectToAction("Index");

}

return ret;

}

I wasn’t particularly happy with this solution as I new I’d be repeating code in my Edit method if and when I get one.

I will try and work out a better solution later on. See Concerns below.

Things left to do

I feel this is getting close to a beta release now. There are three things I want to do before I release the first version:

1) Introduce authentication/registration to be able to post comments.

2) Search for an existing venue when the user adds in a comment

3) Replace the CRUD operations with stored procedures.

4) Tidy up the fields to only show those required.

5) Carry out a security review

6) Investigate producing some unit tests.

Conclusions

  • Do not use the boiler plate code as the exception handling is very dodgy! Specifically it will “swallow” all your exceptions.
  • Use a specific model for your form to model behaviour or data types specific to your view.

Concerns

· One of MVC’s alleged advantages is it “It makes it easier to manage complexity by dividing an application into the model, the view, and the controller”. Personally – I always separate out the UI and business layer anyway and split the business layer into Business Process objects, Business Objects and a Data Access layer. It would seem to me that the Entity Framework has combined my DAL and Business objects and the processes are residing in my model. My concern is that these processes by default have been created as part of the User Interface, and not as a general API in a separate assembly as I would normally do. This will, IMO, make these harder to reuse for example, when I want to expose my business processes (the controller) as web services.

· There seems to be some strange stuff going on in the UI –

<div class="editor-label">

<%= Html.LabelFor(model => model.ViewComment.Venue.vcrTown) %>

</div>

<div class="editor-field">

<%= Html.TextBoxFor(model => model.ViewComment.Venue.vcrTown)%>

<%= Html.ValidationMessageFor(model => model.ViewComment.Venue.vcrTown)%>

</div>
I need to try and understand what this “model => model.ViewComment.Venue.vcrTown” is doing and how. I’ve a feeling its something called Lambda expressions.

Next up – authentication & authorisation

Experimenting with MVC and the Entity Framework – Part 2 – Displaying some data

This is part 2 following on from Part 1.Here I will display some data from my database!

 

clip_image004

[more]

I pretty much used the Nerd Dinner sample to work out what I needed to do. I first of all got the venue types list, details and delete working using the entity framework mapped to stored procedures for CRUD operations. I’ll go into this for Comments in a later blog.

Using my experience gained I moved on to the comments screen. Most of the development here was pretty straight forward following the instructions for Nerd Dinner.

Creating the Entity Model.

I added in a new ADO.net entity model by right clicking in the solution explorer, selecting add new and the “Data” category. I then added entities for each of my tables.

clip_image006

At first glance this object model looks sufficient for the job. Typically I would de-normalise my relational model in my object model but there doesn’t seem to be anything to de-normalise!.

clip_image008

I then created a new View (Views/Comment/Index.aspx) strongly typed to the Comment object and a new controller – CommentController.

clip_image002

Initially the Index method displayed all the items in the table:

//

// GET: /Comments/

public ActionResult Index()

{

victorEntities e = new victorEntities();

// get the venue and location

return View("Index",

e.CommentSet.ToList<Comment>());

}

Mapping query to a stored procedure.

As I previously stated in Part 1 – I intend to use stored procedures for my database access as:

  • they are more secure
  • they are more scalable
  • they allow separation of database schema and application code making the application more maintainable
  • I don’t want to learn LINQ or Entity SQL!

For this particular project the last is probably the most important – but over the last 20 years I’ve witnessed many times the bad consequences of mixing application code and database queries.

I followed the instructions here http://msdn.microsoft.com/en-us/library/dd456824.aspx to import a stored procedure as a function that returns a complex type.

I wrote my stored procedure to return not only the comment but also the associated venue row as well as I wanted to display this:

ALTER PROCEDURE dbo.usp_CommentGetByVenueAndLocation

(

@p_vcrVenueName MediumString,

@p_vcrLocation MediumString

)

AS

SELECT * from tblComment c

INNER JOIN tblVenue v

ON c.intVenueId = v.intVenueId

WHERE (@p_vcrVenueName IS NULL OR v.vcrName like @p_vcrVenueName + '%')

AND (

@p_vcrLocation IS NULL

OR vcrAddressLine1 like @p_vcrLocation + '%'

OR vcrAddressLine2 like @p_vcrLocation + '%'

OR vcrTown LIKE @p_vcrLocation + '%'

OR vcrCountry LIKE @p_vcrLocation + '%'

)

I added a form for the input on the view:

<h2>Search</h2>

<% Html.BeginForm(); %>

<%= Html.Label("Venue") %>

<%= Html.TextBox("txtVenue") %>

<%= Html.Label("Location") %>

<%= Html.TextBox("txtLocation") %>

<input type="submit" value="Search" />

<% Html.EndForm(); %>

And modified the controller method to read the input from the form values colleciton -

public ActionResult Index(FormCollection formValues)

{

victorEntities e = new victorEntities();

// get the venue and location

String location = formValues["txtLocation"];

String venue = formValues["txtVenue"];

return View("Index",

e.CommentGetByVenueAndLocation(venue,location));

}

However, when it came to displaying the Venue attributes I found the venue object had not been loaded and I could find no way of doing so.

So I forced each row to load in the view as it iterates through – really inefficient.

<% foreach (var item in Model) {

item.VenueReference.Load();%>

<tr>

I also found that the columns returned from the stored proc had to exactly match the object attributes even if a mapping had been provided!

I had a poke around the web and the only thing close to what was required was this blog.

This seems a lot of work and the stored proc is not exactly efficient either.

Normally I hand crank my business objects and write a create method for each of my business objects (entities) which takes an IDataReader and so can call this to create any composite objects that are returned from stored procedures as well as providing lazy load via a get by primary key method/procedure for lazy loading.

Whilst doing this research I reached the conclusion that stored procedure support in the entity framework was pretty new and not very mature

Conclusions so far:

  • I would not use Entity Framework on any enterprise project until stored procedure mapping was more mature.

Source

If anyone is interested here is the project as it stands.

This is pretty rough and ready development stuff.

Experimenting with MVC and the Entity Framework – Part 1

If you’ve got an hour – watch this http://channel9.msdn.com/posts/matthijs/ASPNET-MVC-2-Basics-Introduction-by-Scott-Hanselman/

Introduction

For some time now many of my techy colleagues have been pestering me to have a look at MVC and some of the Object Relational technologies emerging from Microsoft.

I have been telling them I’m not interested.

This was a bit of a lie, I am interested but I’m also old. - old enough to have seen some of these magic bullets before.

[more]

I was once involved in a project which all of a sudden had to adopt an MVC framework. In this case it was Apache Struts. Now this is now a pretty mature product but at the time it was new, buggy and the support was very limited. In fact, from what I remember, just about everything in the technology stack was in flux. EJB 2.0 had just come out, the Web Logic server was new, we were using some shaky open source LDAP server etc.

I hasten to add I was not the architect on the project else I would have stuck with the C++ on Sybase that was working for another year or two. Luckily the dot-com bust happened, the bank shed all its contractors and I went next door to another bank for more money on a more stable platform – those were the days.

Well – more stable in terms of the UI technology. However, the other bank had a corporate policy to adopt an Object Relational tool. I believe the Object Relational mapping later became a java standard.

This worked well for development. We built a great business object model for netting trades. Both me (as technical lead) and the architect were heavily into OO. We mapped it to the database schema and it sailed through test.

Then we hit production – firstly we had performance problems. The database was accessed using parameterised queries generated by the tool. The business process of handling a trade was in a transaction that spanned the incoming message Q and the database.

The performance was abysmal and the system kept on deadlocking.

Still, a few indexes and some hand cranking into stored procedures fixed this.

The second issue was harder. Every so often the Object cache in the third party tool would get out of sync with the database. A value displayed to the user would get “stuck” – although fortunately the underlying data in the database was OK.

We flew a support engineer from Canada to try and resolve it. Sat him down until a problem occurred, attached the java command line debugger to the process and gave him the keyboard so he could take diagnostics. At this point he said “I’ve never actually been involved with any coding before!”

Also – this bank later went on to make a few investment mistakes and collapsed creating a global banking crisis. This was not my fault or the fault of MVC.

So, when informed “there’s a great new MVC framework in town and it works great with a new Object Relational mapping technology that means you don’t need to know SQL anymore” my enthusiasm was not exactly high.

Aims

  • to assess the suitability of MVC and the Entity Framework to the Enterprise
  • to investigate these new technologies
  • to produce a working application
  • to have a bit of fun

The project

Some people say I moan a lot. I have been compared to Victor Meldrew – a character from a UK sitcom. To an extent, I agree, like Victor I get flabbergasted by the stupid things that seem to go wrong.

So – here’s the concept – a website called either Victor Meldrew or ImNeverGoingBackThere where people can say what went wrong with places they went to.

Probably stick to simple stuff to start with and then see if people use it

Basic Use Cases.

Display a list of recent gripes.

*The user is displayed a list of recent gripes. Each gripe consists of a venue type, venue name, venue location.

Search for gripes

Comment on a gripe.

Add a gripe.

Tools

Although I have a full licence for Visual Studio P.E. 20xx and SQL server developer 20xx – I decided to use the free versions to make the project useable by all.  I did not use 2010 as this is not supported on XP.  Also, I’ve stopped lugging my laptop around and wanted the whole thing on a memory stick.

So tools are –

Visual Web Developer 2008 Express Edition available free from Microsoft.  I did not use VS 2010 as s

SQL Server 2008 Express.

Windows XP SP2 and Windows 7

I will probably have to deploy on IIS6, Windows server 2003 and SQL Server 2005 as this is what’s installed in the production environment I have in mind.

Approach

I like to start all technical projects with an initial schema design, then build basic object layer on top and implement use cases to fill out the details.

I intend to blog each of these iterations

Initial Schema

Just to finish this blog with a bit of techie stuff, here is the initial schema.

clip_image002

Notes on the schema.

By convention we always create a “non natural primary key”.

Or, in English, create an integer column for the primary key which is an identity – i.e. created by the database. This column is names in<Table Name>Id

This is very good practice as:

  1. The keys created are very good indexes as they are numbers.
  2. Its easy to see from a table definition the foreign key relationships e.g. when I look at tblComment I can see instantly that intVenueId refers to tblVenue. This also makes writing stored procedures much quicker.
  3. A change in business logic or requirements will not suddenly make my key non unique.
  4. The schema is more amenable to change (or refactoring as we like to call it now). We can move any of the other columns during design/development without worrying about the PK.
  5. Generating the number in SQL as an identity means the number is guaranteed unique.
  6. Identity is efficient to generate.

I am sure there are other reasons too.

Put the PK, foreign key and all other constraints in from the start.

So many times I’ve worked on projects where “they’ll put in the constraints at the end”. What when you’re up against the deadlines and neck deep in testing and refactoring as opposed to when your doing design!

Code, technologies, users, operating systems all come and go – data stays around for years, sometimes decades. It’s going to get dirty but the more you can make the database stop this the longer it’ll take.

Create some string user defined types.

I usually define 3 user define types in SQL so I don’t need to worry about mismatches in string lengths between my stored procedures and tables.

ShortString – nvarchar(10)

MediumString nvarchar(100)

LongString – nvarchar(255)

I do this using -

CREATE TYPE ShortString FROM nvarchar(10)

Etc.

Standard columns for audit.

I also add in at least three columns for audit purposes.

I might also add in a couple of other columns on each table

intSessionId – the users login session.

intRequestId - a number for the page request

These two can be used when I want to see what data was updated by a particular user for a particular session/login and during a particular change request.

Normally I would use SQL management studio to create the database and initial schema but this is not in my toolset – so I used VS. Unsurprisingly it was pretty much the same.

In part 2 I Display some data!