Entity Framework

.Net 4.0 Beta 2 Entity Framework – How To Set Up Complex Types, Now With More POCO

So something I’ve seen but just have now conquered is the whole complex type thing in Entity Framework. Not only that, but with the cool new Persistence Ignorance thing all the kids are talking about. (And boy am I glad there’s a new kind of ignorance on this site now.) You might ask: What’s a complex type? Course you might ask: Why is it so hard to make a decent movie about huge robots that transform into cool vehicles and have really big guns? Well I can answer the first question.

Complex types, in the simple minded way I see them, are a way to split information retrieved from a table into a object within an object. Confused yet? I hope so.

Say you have a User table and on that table you have the typical user information including a bunch of stuff like Name, Sex, City, State, and Zip. Now you could set up the user class to look like this:

  public class User
  {
    public String Name { get; set; }
    public String City { get; set; }
    public String State { get; set; }
    public String Zip { get; set; }
  }

Nothing wrong with that, but what if you have an Address class:

  public class Address
  {
    public String City { get; set; }
    public String State { get; set; }
    public String Zip { get; set; }
  }

That you wanted to map the values from the User table to the User class to so that the User class looked more like this:

  public class User
  {
    public String Name { get; set; }
    public Address Address { get; set; }
  }

Well I’m here to tell you that you can and I have plenty of pictures to back it up.

Keeping with the design from this guy, I am going to add a new class called AdDimension which will have Height and Width, effectively replacing the Height and Width properties on the Ad class.

RECAP – Old class diagram:

So the first thing I have to do is is create the complex type in the Model Browser. That’s pretty easy. There’s a section called “Complex Types” and you just add one.

See, there’s a picture. Next step is adding properties to the newly created complex type.

Another picture! Ok so now you’ve created on, big deal. Now what? Well you have to add a complex type property to the main class, Ad in this case.

Now if you have more than one complex type, you might have to set the property type to the correct one. Just right click the complex property and click on properties:

Now, you’ll have to adjust the mapping for the entity itself. So click on the object and bring up mapping properties. From there, select the correct mapping for the correct column. In this example it’s width and as you can see in the drop down there is a AdDimension.Width property to set it to. Cool huh?

And that’s it for the object model. Next you have to add the AdDimension class and add a property to the Ad class.

  public class AdDimension
  {
    public Int32 Height { get; set; }
    public Int32 Width { get; set; }
  }

  public class Ad
  {
    ...
    public AdDimension AdDimension { get; set; }
    ...
  }

And then how do you set it?

  someAd.AdDimension = new AdDimension();
  someAd.AdDimension.Height = 10;
  someAd.AdDimension.Width = 10;

And it can even be used in queries:

  context.Ads.Where(ad => ad.Width > 10);

Now there are things to be aware of. Complex types can’t inherit from other complex types and the property can’t be null when saving. Just be aware of that.

I’m telling you man, this s– is the s— man. It’s the s—.

.Net 4.0 Beta 2 Entity Framework – Many To One and POCO / INSERT statement conflicted with the FOREIGN KEY constraint issue

So the next step in the New Entity Framework saga was to make a many to one relationship and get it to save. After all, with the last version I had far more issues with many to one than any other kind of relationship. Turns out, the steak is still in tact.

Taking the structure from the last post, I added an AdType. It’s very simple. Columns are AdTypeId (Int Primary) and Description (Varchar). I added a AdTypeId column to the Ad table and created a foreign key to the AdType table with it.

Basically Many Ads to One AdType.

Then I did the usual Update Model From Database with the .edmx file. Same old same old. Well this added the AdType class and the relationship:

So far, so good. Now with this it also means that I need to create the AdType class and add the AdType property to the Ad class along with the AdTypeId property.

Side Note:

Far as I can tell, you need the AdTypeId property despite also having the AdType property. Don’t forget this.

Here’s the AdType class:

  public class AdType
  {
    public Int32 Id { get; set; }
    public String Description { get; set; }

    public virtual IList<Ad> Ads { get; set; }
  }

And on the Ad class I had to “Ad” (HARHRHARHR) the AdType property along with the AdTypeId property:

  public class Ad
  {
    public Int32 Id { get; set; }
    public DateTime CreatedDate { get; set; }
    public String Name { get; set; }
    public DateTime? LastUpdated { get; set; }
    public Int32 Height { get; set; }
    public Int32 Width { get; set; }

    //Interesting note: Works even if this is private and non virtual
    private Int32 AdTypeId { get; set; }

    public virtual AdType AdType { get; set; }
    //Note:
    //If properties are to be lazy loaded, must be virtual
    public virtual IList<Newpaper> Newspapers { get; set; }
  }

Classes are done. Should be good to go right? WRONG YOU ARE SO F-ING WRONG! Just try this, I dare you:

   Ad ad = new Ad();

   ...
   ad.AdType = someAdType();
   context.Ads.Add(ad);
   context.SaveChanges();

DUN DUN DUUUUN INSERT statement conflicted with the FOREIGN KEY constraint.

You try it? Idiot. You just got stuffed by a foreign key error. Why? Well The Big M has an answer:

In this example, Customer is a pure POCO type. Unlike with EntityObject or IPOCO based entities, making changes to the entity doesn’t automatically keep the state manager in sync because there is no automatic notification between your pure POCO entities and the Entity Framework. Therefore, upon querying the state manager, it thinks that the customer object state is Unchanged even though we have explicitly made a change to one of the properties on the entity.

Basically because these objects aren’t really being watched by the State Manager, it has no idea anything has changed and should be persisted. Therefore, it has no idea that the AdType property has changed and that the AdTypeId should be updated to reflect this. That’s right, it just ignored the AdType property and left the AdTypeId to it’s default… 0 (That’s a Zero or as the English say, Zed). How do you get around this? This little option.

  context.SaveChanges(System.Data.Objects.SaveOptions.AcceptAllChangesAfterSave);

This tells it to persist the changes that it didn’t know about. Now I’m still picking this up as I go so bare with me as I might have the best explanations yet on why things work with the New Entity Framework. Then again, this is a site by a tool.

.Net 4.0 Beta 2 Entity Framework – How To Start

So I just recently turned my laptop into a 4.0 workstation since it is kind of expendable and I won’t feel inclined to nerd rage if it gets tooled. With this step forward, I decided that I probably won’t be doing much with the old Entity Framework version since the new one is supposed to be the end all/be all until the next end all/be all version comes out. What does this mean for you? This post could be filled with useful information or f— all. Just depends on what you care about.

After doing a little reading, and by little I mean as little as humanly possible while still having an idea of what I’m doing, I got the new EF to persist an object. Mind you, it took a bit of patience followed by slamming my head into my desk and then more patience, but I did it.

First off, the actual creating of the edmx file is the same, there’s really no difference. You still go through the wizard, you still go through the connection set up, and you still grab the tables you want. No change there.

However, once you have it created, click on the .edmx file in your solution explorer, right click, then click properties. You’ll see something like this:

You can see where there is nothing in the Custom Tool area of properties. That’s because I deleted the text. That’s the first part of what you need to do. Next is creating the needed classes. For this example I have two, Ad and Newspaper:

namespace Beta2Test.Data.Entity
{
  public class Ad
  {
    public Int32 Id { get; set; }
    public DateTime CreatedDate { get; set; }
    public String Name { get; set; }
    public DateTime? LastUpdated { get; set; }
    public Int32 Height { get; set; }
    public Int32 Width { get; set; }

    //Note:
    //If properties are to be lazy loaded, must be virtual
    public virtual IList<Newspaper> Newspapers { get; set; }
  }
}

and

namespace Beta2Test.Data.Entity
{
  public class Newspaper
  {
    public Int32 Id { get; set; }
    public Int32 Circulation { get; set; }
    public DateTime CreatedDate { get; set; }
    public DateTime LastUpdated { get; set; }
    public String Name { get; set; }

    //Note:
    //If properties are to be lazy loaded, must be virtual
    public virtual IList<Ad> Ads { get; set; }
  }
}

Three things you might notice:

The classes don’t inherit from anything. Yay
The namespace is Beta2Test.Data.Entity. Fact is, I left it there to show that the namespace can now be anything. Yay
Both collection properties are virtual. This is a must when dealing with collections that represent a relationship and you have lazy loading enabled (See below context class). For this, there is a many to many relationship between Ads and Newpapers. The Ads and Newspapers collection represent that. Why do that have to be virtual? Has to do with Entity Framework needing a way to override the properties so it can tell when they are accessed. (Read: Lazy Loading)
There is no fourth thing. You are wrong if you think there is.

So far so good, but what about that like whole linking to that persistence layer stuff. You know like them entity objects yo.

Thank you for that poorly worded inquiry, but valid none the less. Remember those entity context classes that it used to generate for you? Well that whole “custom tool” dohickey I removed would have built it for me. However, that’s not the path we’re going down anymore. It’s now time to brave the unknown. We, yes we, will build the context. Yes the context.

using System.Data.Objects;
using Beta2Test.Data.Entity;

namespace Beta2Test.Data
{
  public class Beta2TestContext : ObjectContext
  {
    private ObjectSet<Ad> _ads;
    private ObjectSet<Newspaper> _newspapers;

    public Beta2TestContext() : base("name=InterviewDemoEntities", "InterviewDemoEntities")
    {
      _ads = CreateObjectSet<Ad>();
      _newspapers = CreateObjectSet<Newspaper>();

      //This makes sure the context lazy loads by default
      ContextOptions.LazyLoadingEnabled = true;
    }

    ///
    /// This is used to set up the "queryable" collection.
    ///
    public ObjectSet<Ad> Ads
    {
      get
      {
        return _ads;
      }
    }

    public ObjectSet<Newspaper> Newspapers
    {
      get
      {
        return _newspapers;
      }
    }

    ///
    /// This creates an ad that allows it "to be used with the Entity Framework."
    ///
    ///
    public Ad CreateAttachedAd()
    {
      return EntityContext.Context.CreateObject<Ad>();
    }

    public Newspaper CreateAttachedNewspaper()
    {
      return EntityContext.Context.CreateObject<Newspaper>();
    }
  }
}

The CreateAttached methods aren’t needed on the Context class itself, I just put them there. They could go on the Ad/Newspaper class but at this point I’m not sure if that blurs the lines or not. Haven’t gotten into best practices mode yet.

You may notice the ObjectSet collections on the context. These are the collections you will query to get items most likely, much like the old context class from the last version:

Context.Ads.Where(ad => ad.Id == 1)

Pretty nice, huh?

Also, I noted above about the virtual properties on the Ad/Newspaper classes and how it had to do with lazy loading.

ContextOptions.LazyLoadingEnabled = true;

That line and the virtual properties ensure that lazy loading will occur when the property is used. Both have to be used together. Now you don’t have to put that line in the constructor if you do create a context every time you are retrieving things, you could just set it on a case by case basis. Up to you. I use a singleton like context so I just set it true in the constructor and don’t worry about it.

The main thing I had trouble with was this line:

public Beta2TestContext() : base("name=InterviewDemoEntities", "InterviewDemoEntities")

Because I wasn’t sure what the second string should be. First one is easy, it’s whatever the key in the config file for the connection string. Second, chances are, is the same depending on how you set it all up in the wizard. I think it’s the same name when you right click on the opened .edmx file and view properties. There should be a property named “Entity Container Name”.

Here’s a method I used for testing to create an Ad:

  public Ad CreateAd(Boolean persist)
  {
    Ad ad = null;

    if (persist)
    {
      ad = EntityContext.Context.CreateAttachedAd();  //This is the CreateAttachedAd method above in use
    }
    else
    {
      ad = new Ad();
    }

    ad.CreatedDate = DateTime.UtcNow;
    ad.Height = RandomTool.RandomInt32(0, 10);
    ad.LastUpdated = DateTime.UtcNow;
    ad.Name = RandomTool.RandomString(10);
    ad.Width = RandomTool.RandomInt32(0, 10);

    if (persist)
    {
      AddEntityForRemoval(ad);  //Ignore, for persistence removal after testing is completed
      EntityContext.Context.Ads.AddObject(ad);  //Ad the ... ad to the context
      EntityContext.Context.SaveChanges();  //Persist
    }

    return ad;
  }

There are some things you can ignore because I didn’t feel like removing code. As you can see though, it’s not very complicated. Use the CreateAttachedAd, fill in the properties, ad to the Ad collection on the context, and save.

The End

Sure you had to do more work than in the earlier version of Entity Framework, but on the other hand I have a fully detached class that can inherit from any class I WANT it to as opposed to the EntityObject class. Not to mention I now have an easy way to do lazy loading. Far cleaner than the old way I did it. The other interesting thing to note, and this may not be a big deal to anyone else, but above I have a note about this:

  AddEntityForRemoval(ad);  //Ignore, for persistence removal after testing is completed

This method just adds the object to a collection of EntityObjects that later I use to delete the object from the database on test cleanup. After moving to this version of EF, I had to change it to a collection of Objects. Interesting thing is:

  EntityContext.Context.DeleteObject(currentObject);

Didn’t care. It still knew that the object was attached to the context somehow, despite the CLASS not being an EntityObject. Just an odd note.

Two Errors I came across:

System.InvalidOperationException: Mapping and metadata information could not be found for EntityType ‘Beta2Test.Data.Ad’.

If you get this, there is a really good chance you screwed up a property name or are missing it completely. If the property name doesn’t exactly match one in the .edmx class designer template, you’re screwed. If you don’t have it on the class but it’s on the class designer template, you’re screwed. If you add it to the class designer template, it has to be on the class as far as I can tell. Now, it doesn’t have to be public. I have tested it as private and it works just fine.

The required property ‘Newspapers’ does not exist on the type ‘Beta2Test.Data.Ad’.

This most likely happens if you didn’t remember to make it a property:

public IList<Newpaper> Newpapers;  //Forgot the { get; set; }

public IList<Newpaper> Newpapers { get; set; };  //Forgot the Virtual

Opps.

Entity Framework: Am I Just Being Stubborn?

Before I started using The EF, I had a couple years of use with LLBLGEN and about a year into NHibernate. I started dabbling with Linq to Sql for a bit but found it a bit lacking (As it was new, go figure) and decided to go forward with Entity Framework due to the fact that I really like Linq, and although LLBLGEN had Linq integration, I figured the Entity Framework would be the first out there with any changes in Linq. (Not exactly sage like foresight) And at the time HQL was still the thing for NHibernate, and although it’s not a bad design, it’s a nightmare waiting to happen since you can’t get a compile time check on the HQL strings. So it seemed like the right idea at the time, and despite what I’m about to write, it may still be.

I saw a lot of people bagging on it saying that it was missing this feature or that feature. Most people were annoyed that there wasn’t built in lazy loading. Some were annoyed with the fact that the classes are coupled to persistence. Others just complained because it wasn’t [insert your ORM here]. I on the other hand championed the cause with this in mind:

It’s just a framework. The tools are there do what you need, you just need to build it.

Take as is, the word framework is just that. It’s just a start. It’s that point where a house is just a bunch of wood nailed togther. You can’t really live in it as is… well I suppose you could?… but it has what you need to produce a complete house. Just takes a little time and effort. You can already see where this is going. After all, I’ve already figured out I like pain.

So the first issue I ran into was the context itself. You see, I was already used to multiple calls to the database being done with one “connection” and by that I mean not this every time I wanted to get something:

  using(var entity as ToolEntities)
  {
    entity.Tools.Select(...)
  }

Mind you my syntax is more psuedo in that example, but the idea was simple; It was going to be annoying to have to do that every time I wanted something from the database. So after hours of thought and some research, I came up with a solution that would open one “connection” per request that all queries within that request would use. Ok so that was out of the way, and I can’t compain since after all:

It’s just a framework. The tools are there do what you need, you just need to build it.

The next thing I ran into was lazy loading a many to one or one to one property. I had already seen stuff on many to many or one to many loading. Simple method .Load(). To my surprise there wasn’t such a method for this (Not directly at least) so I was stumped. Time to go back to LLBLGEN? Hell no, mush on. And mush on I did. I found the .Include method:

  context.Tools.Include("Favorites")

Boo yah, and I’m back in the game… or was for a little bit until I realized that this is pretty bad since Include loads the whole object you are including. This could be ridiculous if you have an object with 10… 100… ONE MILLION many to one or one to one properties. So once again, hours of thought and research later I came up with a solution that was eh good enough but wasn’t exactly what I’d hoped for.

It’s just a framework. The tools are there do what you need, you just need to build it.

Another issue I ran into was a creating an IN clause method. Contains just doesn’t work with Entity Framework. Straight up, flat out, no can do sir. So again, with time and research I found a way to do this (Which I haven’t posted yet due to the fact I didn’t come up with the solution and I can’t find the link I got it from). Of course there was the order by situation, oh and this gem, and this that I can only describe as a god damned mess. Really? It’s in the context yet you still have to run a query on it? Really??

I’m not going to quit on Entity Framework just yet but after a little introspection I have to admit that I’m close to responding to my own righteous:

It’s just a framework. The tools are there do what you need, you just need to build it.

With a big :

Go f- yourself.

This whole situation reminds me of a saying:

If you’re in a poker game and you can’t figure out who the patsy is, you’re the patsy.

It doesn’t take much imagination to apply that saying to my situation and to be honest I might be falling into the same situation with the MVC framework. Time will only tell with that. Although at least with MVC I have plenty of patsies with me. I think with Entity Framework, I might be the only one losing money.

Entity Framework: LINQ to Entities only supports casting Entity Data Model primitive types

So in typical tool fashion I posted this little gem without realizing a glaring error… the order by clause. The whole idea is to create a method that can get a collection, sort it, then grab a certain number for paging. The issue was this:

    Expression<Func<K, IComparable>> orderBy

The problem comes in when the entire expression is run, meaning when Entity Frame work takes:

    context.Select(selectMethod).Where(whereClause).OrderBy(orderBy).ToList();

and creates SQL out of it. Entity Framework doesn’t really know how to handle IComparable as it has not primitive/sql type to match to it. Why it can’t see the underlying type of say DateTime, no idea, but this is life.

So this should be an easy fix, right? Eh… yeah. First I thought instead of IComparable I could just convert to some kind of primitive type so that the EF could be on it’s merry. Not so much. Turns out this probably isn’t possible.

Well thanks to a push from Ben M at The O Flow I got to thinking about how to attack this. Instead of sending in an expression for the order by, why not send in a method that would take in a query and tell it how to order itself. Sounds hard right? (If not then you’re obviously too cool for this school) Well it’s not, just a different way to think about it.

Right off the bat, the change to the method signature would look like this:

Old order by parameter signature:

    Expression<Func<K, IComparable>> orderBy

New order by parameter signature:

    Func<IQueryable<T>, IOrderedQueryable<T>> orderBy

So what does that mean? It means that I am going to supply the method with a method that will take in a query and return an ordered query… well not ordered yet per se but the blueprint on how to order when that time comes around. Now here’s how that’s used:

First you need the query:

    var initialQuery = query
    .Where
    (
        somethingEqualsSomething
    );

Then you apply the orderby method, and in the case of the original paging method, the paging too:

    var orderedQuery = orderBy(initialQuery);

    returnValue = orderedQuery
      .Skip(numberToShow * realPage)
      .Take(numberToShow)
      .ToList();

So overall, doesn’t look too much different. Just instead of supplying the OrderBy method with a Func, you give a method that creates an ordered query.

How would you use this? Remember the signature was (whereClause, selectClause, orderBy, pageNumber, numberToShow, realPage, totalCountOfPages)

    Tools.GetListForGrid
    (
      tool => tool.EntityId == userId,
      tool => new { Name = tool.Name },  //Select Clause, I'll get to that next
      toolOuter => toolOuter.OrderBy(toolInner => toolInner .Name),  //OrderBy
      ...
    )

Two things you might notice. One would be the OrderBy signature:

   toolOuter => toolOuter.OrderBy(toolInner => toolInner .Name),

What the hell? Remember the method you are sending takes in a query and returns an ordered query. toolOuter is your query, toolOuter.OrderBy(toolInner => toolInner .Name) is your blueprint (IOrderedQueryable) on how it should be queried.

Second thing is that when I showed how to use the OrderBy method above:

    var orderedQuery = orderBy(initialQuery);

    returnValue = orderedQuery
      .Skip(numberToShow * realPage)
      .Take(numberToShow)
      .ToList();

I didn’t include the select clause. Partially because I didn’t want to complicate it yet, partially because it has it’s own issue. If you’re like me, you use the select clause a lot. Why? Because it limits the amount of information you take from the database. Say if you are trying to fill a drop down list, why select an entire Tool object (Which could have a ton of mapped properties) when all you need is Id and Name? Seems silly. That’s where the Select clause comes in. Now the issue is where to put the order by. You would think after the select clause, since you want to sort on only what you are selecting. Problem is, with paging that gets screwed up. The way sql server “pages” is that it selects twice.

Select all the things that match this where clause.
Select a certain number of items from that starting at X.

The first select creates a dataset with row numbers, and the second one selects the items based on those row numbers. IE take 5 starting at row 5. Now the way the EF handles the order by is to grab the info you need from the Select (Ordered by something… you don’t get to choose) and THEN order and grab. As you can guess this may not give the needed results. So how can this be solved? Order before the select as witnessed in the new and improved method:

public static IList<K> GetListForGrid<T, K>
(
    this ObjectQuery<T> query,
    Expression<Func<T, Boolean>> somethingEqualsSomething,
    Expression<Func<T, K>> selectClause,
    Func<IQueryable<T>, IOrderedQueryable<T>> orderBy,
    Int32 pageNumber,
    Int32 numberToShow,
    out Int32 realPage,
    out Int32 totalCountOfPages
)
{
    IList<K> returnValue;

    Int32 totalItemCount =
        query
        .Count
        (
          somethingEqualsSomething
        );

    totalCountOfPages = Methods.TotalCountOfPages(totalItemCount, numberToShow);
    realPage = Methods.GetRealPage(totalCountOfPages, pageNumber);

    var initialQuery =
      query
      .Where
      (
        somethingEqualsSomething
      );

   var orderedQuery = orderBy(initialQuery);

    returnValue = orderedQuery
      .Select
      (
        selectClause
      )
      .Skip(numberToShow * realPage)
      .Take(numberToShow)
      .ToList();

      return returnValue;
}

And usage:

    Tools.GetListForGrid
    (
       tool => tool.Id == userId,
       tool => new { Name = tool.Name },  //Select Clause
       toolOuter => toolOuter.OrderBy(toolInner => toolInner .Name),  //OrderBy
       pageNumber,
       amountToShow,
       out realPage,
       out totalCountOfPages
    );

Now this one actually works with Entity Framework and not just Linq to objects like the last one.

Entity Framework: Reusable Paging Method

OBSOLETE… sort of. Found an issue with this and am posting the issue today. (07/21/2009)

A while back I decided to show some paging stuff and I forgot that recently, well ok in the last few months, I actually put together a sort of generic extension method to take care of most of the paging situations I was running into. Reason being is I got tired of writing the same method over and over. I figured I’d share it in hopes that somehow, somewhere, it would keep someone’s dream alive. That and I figured I needed to make up for not posting for a while… which to some of you might have been a good thing.

Anywho, it’s not to difficult actually. Small note though, when reading the code you will see the TotalCountOfPages method and the GetRealPage method. If you can’t figure out what those are then I just don’t know what to do. (HINT: They are linked somewhere in this very post! Side note: This post is on track to match the normal self linking per post quota on atypical Codding Horror post)

public static IList<K> GetListForGrid<T, K>
(
  this ObjectQuery<T> query,  //Extension method syntax... basically takes in any SomeEntities.SomeTableRepresentation
  Expression<Func<T, Boolean>> somethingEqualsSomething,  //Where clause
  Expression<Func<T, K>> selectClause,
  Expression<Func<K, IComparable>> orderBy,  //Turns out order by just needs anything that is IComparable
  Int32 pageNumber,
  Int32 numberToShow,
  out Int32 realPage,
  out Int32 totalCountOfPages
)
{
   IList<K> returnValue;
   //Get the total count of the items.  Need this to do proper paging.
   Int32 totalItemCount =
      query
      .Count
      (
         somethingEqualsSomething
      );

   totalCountOfPages = TotalCountOfPages(totalItemCount, numberToShow);
   realPage = GetRealPage(totalCountOfPages, pageNumber);

   returnValue = query
   .Where
   (
      somethingEqualsSomething
   )
   .Select
   (
      selectClause
   )
   .OrderBy(orderBy)
   .Skip(numberToShow * realPage)
   .Take(numberToShow)
   .ToList();

   return returnValue;
}

As you can see there are a lot of expressions going on and that’s the way this works. It takes care of the annoying stuff that you would have to copy an paste while you supply it the stuff that changes like what you want to select and what the needed items have to match to be selected. Even has something for an order by. What the usage look like?

  ToolEntities.Tools.GetListForGrid
  (
    something => something.SomethingId == passedInSomethingId,  //Where clause
    found => found,  //Select: Simple here.  Usually I use classes to hold info... next post
    found => found.Name,  //Order by
    pageNumber,
    amountToShow,
    out realPage,
    out totalCountOfPages
  );

Really simple, eh?

Why totalCountOfPages and RealPage out? Well the whole idea is that this infomation is valuable to the picker control you will be constructing outside this method call. RealPage is nice for showing what page the pager is on and how to figure out what the previous and next pages will be. TotalCountOfPages is useful for the last page possible. Now the nice thing about the RealPage method is that it won’t give you a non existent page. It will only give you the best possible page if the pageNumber parameter is over the possible amount of pages.

Also do notice that this is an extention method meant to be used with the Entity Framework’s built in system of handling the Table representations say ToolEntities.Tools.(Method will show up here in intellisense). This makes it cleaner to me than having to pass in the actual current context. As in:

GetListForGrid(ToolEntities.Something, …) vs ToolEntites.Something.GetListForGrid(…)

In the end, either way is fine.

Entity Framework: Possible Lazy Loading Solution

So been a while since I posted last, and I’m sure everyone has been worried. Turns out that I’ve been banging my head against the wall named MVC. And man I have a slew of new posts back logged for when I have more time. However, due to the new project I’ve been working one, I forced myself to tackle Lazy Loading in the Entity Framework. Turns out the solution isn’t that hard since it has everything needed to make it work.

One of the biggest waaaaahbumlance calls is the fact that Entity Framework doesn’t have lazy loading. This isn’t true. It does, you just have to do a little work, which is to be expected from a FRAMEWORK. Now I have to admit, I wish there were a slightly less odd way of doing this, but eh it’s not too bad.

Say you have a class named Topic and it has a reference to a Forum class named ParentForum. Now this is just a property on the actual class created by the E-Work to represent a many to one foriegn key relationship between the Topic table and the Forum table. Now the problem comes in when you try to use something from that property like:

    Int32 someInt = topic.ParentForum.ForumId;

If you didn’t use the Include method when you got that topic:

    context.Include("ParentForum").Select(topic => topic);

You are screwed because the ParentForum property will return a null. So this forces you to use Include every time you want to get a Topic and access it’s ParentForum property. Sucks.

So next thought is to find out how to lazy load and then plunk it into that generated property. Eh I don’t know about you, but that sounds like a bad idea to mess around with a generated file. You have no idea when the E-Work will just trash the file and rebuild.

Next thought was to create a proxy property to check the real one and then load if it’s not already loaded. Something like:

    public Forum ParentForumLazyLoaded
    {
        get
        {
           //check and return
        }
    }

Ok so that’s not horrible except two things: The name looks dumb and people can still get at the original ParentForum property.

So in comes my solution and it’s pretty simple. Rename the property name on the class to something ParentForumInner and make it private.

Then you can create a much nicer outward facing property like:

    public Forum ParentForum
    {
      get
      {
          if (!ParentForumInnerReference.IsLoaded)
          {
              ParentForumInnerReference.Load();
          }

        return ParentForumInner;
    }
    set
    {
        ParentForumInner = value;
    }
}

Now one of the issues with this is that although outside the class only the ParentForum property can be seen, within the class they both can be seen which can lead to issues like:

    context.Topics.Where(topic => topic.ParentForum.ForumId == id);  //boom

Where you should be using:

    context.Topics.Where(topic => topic.ParentForumInner.ForumId == id);  //yay

E-Work doesn’t know how to handle that situation when evaluating that expression and the kicker is that it does just fine compile time. You won’t know this is bad until runtime.

Other issue is that you have to do a little more work then just some setting on the property which is what most people wish for. I guess that may be true, but again this is a framework. It gives you all the tools you need to makes things work, you just have to do some coding.

The plus side to this is that with minimal coding, you can completely get rid of Include which to me is huge.

Entity Framework, Include, and Data Item classes

So found something interesting out the other day, and maybe you already knew this but I didn’t so I’m going to post about it. Don’t like it? Too bad.

One things nice about the Entity Framework is the ability to create or fill a class on the fly with the Linq query methods/language. You.. you have no idea what I’m trying to say. Fine. I’ll put it in programming speak:

  someList.Select(item => new { item.Name, item.ID })

  or

  from item in someList
  select new { item.Name, item.ID }

  someList.Select(item => new DataItemClass { Name = item.Name, ID = item.ID })

  or

  from item in someList
  select new DataItemClass { Name = item.Name, ID = item.ID }

As you can see, they are two slightly different examples. One will create a list of anonymous types and the other a list of DataItemClass objects. Now that’s not really the point of this, the point is something like this:

  context.Topics
    .Select(topic =>
                new TopicItem
                {
                  TopicName = topic.Name ,
                  Creator = topic.User.UserName,
                  Category = topic.Category.Description
                }
              )

Now, here’s the thing that I assumed. When you select out a bunch of topics and you want to make sure that the one to on properties are “include”ed like thus:

  topicList
   .Include("User")
   .Include("Category")
   .Select(item => item);

Other wise when you try to access that User property you’ll get a null reference exception. So with this I thought that when you query like I did above to fill the TopicItem object, I would get the same problem as I would if I tried just accessing the property on a hydrated Topic. In other words:

  Creator = topic.User.UserName,

Would blow up mid query after all calling that property after a query would sans include. Come to find out, and I probably should have already known this, Entity Framework is just fine without having to have the Include method or any kind of join. Infact, adding the include method in the method chain:

  context.Topics
    .Include("User")
    .Include("Category")
    .Select(topic =>
                new TopicItem
                {
                  TopicName = topic.Name ,
                  Creator = topic.User.UserName,
                  Category = topic.Category.Description
                }
              )

Did nothing when I profiled the query. The SQL was exactly the same with or without the include. So it’s smart enough to know that if I am not actually hydrating an entire Topic object, it doesn’t need to bother with the includes. Go figure.

Just Use the Stupid Functionality

So something I’ve come across time and time again since starting to use ORMs (about 3 or so years ago) was the infamous “hydrate a reference object just to save another.” If you haven’t had this before, you probably will anyhow, but here’s what it is. Say I have a “Manny” object and I need to assign some kind of “fertility drug type” to it. Now this “fertility drug type” is basically representative of a table that has an id and description and nothing else really. Now the question is, if I know the ID of “HCG” is 1, should I bother even getting it from the database? I mean after all:

  Drug totallyNotUsingVitamins = toolContext.Drugs.Select(drug => drug.Name == "HCG");
  manny.DrugOfChoice = totallyNotUsingVitaminS;

Looks pointless when I can do this:

  playersObject.DrugTypeReference.EntityKey =
     new EntityKey("ToolEntities.BallPlayers", "drugOfChoiceID", 1);

Which was taken from here.

So here’s the problem: Why should I have to hit the database if I know what the ID is? I mean, that’s a trip I don’t have to make right?

If you’re asking that question, then it’s possible you don’t understand how a lot of ORMs work, or in this site’s case: The Entity Framework.

What it really comes down to a couple arguments:

What is the cost if it hits the database?
Is it actually hitting the database?
It’s just easier to set the ID

What is the cost if it hits the database?

Now the first one you could almost make an argument, after all what’s the point of hitting it if you don’t need to? This might seem silly:

  using(ToolEntities toolContext = new ToolEntities())
  {
    Drug totallyNotUsingVitamins = toolContext.Drugs.Select(drug => drug.Name == "HCG");
    manny.DrugOfChoice = totallyNotUsingVitaminS;
  }

If that is the only time you’re using this. A one off like that does seem to beg overdoing it. But really is it? This is probably the strongest argument for the straight ID set and really what is the real cost? In order to persist the object, a connection has to be created and the entity has to be persisted. So in reality, you have to create a connection any how, so the actually getting and hydrating of the reference object is just sort of tagged onto that and unless there is something wrong with the table or connection, this adds a minimal amount of time.

Is it actually hitting the database?

But what if this has to be run a million times? Then what? I have to get that item a million times and now my hits have gone from one million to two million?

  using(ToolEntities toolContext = new ToolEntities())
  {
    for(Int32 loopCounter = 0; loopCounter < someNumber; loopCounter ++)
    {
      Drug totallyNotUsingVitamins = toolContext.Drugs
                                       .Select(drug => drug.Name == "HCG")
                                       .First();
      manny.DrugOfChoice = totallyNotUsingVitaminS;
    }
  }

Ok not the best example, but the point is that within that using you will be updating a million records which you would assume that

  Drug totallyNotUsingVitamins = toolContext.Drugs.Select(drug => drug.Name == "HCG");

Would be run a possible two million times. You could make the argument that Entity Framework does make a select against the database even if the object is in the context. This is something I’ve thought rather odd myself. However, it’s just a query on an already open connection. And since creating the query is done the first time around, the acutal query being run is minimal in effort. Basically the hard work was does the first time around. The next time is nothing to really consider.

It’s just easier to set the ID

Ok so this one is hard to discredit since it is easier. It’s about one line less easier. Is one line really worth this? Is it worth the ease of understanding? From the first example, it’s pretty easy to understand what I’m setting. Select a drug type of “HCG”. Second one, not so much. All I see is an ID, so if I want to know what drug that is, I’d have to go searching for what that ID is. Also, from a spec level, it’s more clear to say:

Set DrugOfChoice to “HCG”

Then

Set DrugOfChoice to 1

Now I may be reaching on this last example, but as I said, it will be hard to prove that the second example isn’t “easier” coding wise. After all it’s one less line. However, when things like readablity comes in, I can’t see the second being a winner in that contest.

Once a person understands how an ORM like the Entity Framework works, it becomes apparent that some of the old ways of thinking need to be abandoned as in the long run they only hold overall development back. And beyond that, sometimes when we think we’re optimizing, we could actually doing more harm than needed. Losing readability for the sake of ridiculously small amounts of time just doesn’t make sense anymore.

Paging and the Entity Framework, Skip, and Take Part 3

Get the total count of pages. | Get the real page number. | Using Skip and Take to Page | The Actual Paging Controls

Ok so the last two posts have been arguably useless, maybe more so than anything else here, but they were somewhat needed because now I am going to show how to Linq, the Entity Framework, and well that’s it I think.

public static IList<ToolItem> GetSomeTools(String name, Int32 numberToShow, Int32 pageNumber, out Int32 realPage, out Int32 totalCountOfPages)
{
  //EntityContext.Context is just a singletonish version of the
  //Entities class.  Most people would use
  //  using (ToolEntities context = new ToolEnties())
  Int32 totalCount = EntityContext.Context.ToolItems
		   .Count(item => item.Name == name);
  //This is the method from the first post of this series
  //Just getting the count of pages based on numberToShow and
  //item totalCount
  totalCountOfPages = TotalCountOfPages(totalCount, numberToShow);
  //This is the method from the second post of this series
  //Basically getting the best possible page if the page number
  //is higher than the totalCountOfPages or lower than 0
  realPage = GetRealPage(totalCountOfPages, pageNumber);

  returnValue = EntityContext.Context.ChatRooms
			  .Where(item => item.Name == name )
			  .OrderBy(item => item.Name)
			  .Skip(numberToShow * realPage)
			  .Take(numberToShow)
			  .ToList();

  return returnValue.ToList();
}

Really simple yes? It follows like this:

Say I’m on page 1, which for this would be zero or pageNumber – 1. So I want to grab the first 20 items from the database. Well that means I want to start at 0 and grab 20. Now if you want this all to be done with some kind of conditional thing that either handles the first page or the other pages, you actually want to skip the same way no matter what the page number is. This is taken care of by numberToShow * realPage since even at 0 this works. After all 0 * anything is 0 and therefore you will be Skipping 0 items. So in other words, you’re at the start. Next you want to Take the amount of items you need, which is 20. Next time around you’ll start at 20 Skip(numberToShow 20 * realPage 1) and take the next 20. Nice thing is, even if you say Take 20 and there are only 2 left, it doesn’t care. It will just grab those two.

And there you have it, how to page with the Entity Framework and minimal amount of work. I know I hate taking other people’s methods (Like the TotalCountOfPages and GetRealPage methods), don’t know why. So sorry if I am forcing you to do so. However, the two methods I gave are semi important to this.

You might wonder why realPage and totalCountOfPages, well this is useful stuff when knowing what page is next for paging controls. Next post I’ll show those off but I’ll warn you, they are nothing spectacular.