Tag: Entity Framework

So in THIS MIGHTY POST I asked why Entity Framework context likes to query the database despite the object (data row) already being loaded into the context. Well even though it’s not a perfect answer, Craig Stuntz had an answer at The O that gave some light to the situation:

context.SiteUser is an property of type ObjectQuery. When you execute an ObjectQuery, it will always hit the backing store. That’s what they do. If you don’t want to execute a database query, then don’t use an ObjectQuery.

Furthermore I could use GetObjectByKey:

GetObjectByKey tries to retrieve an object that has the specified EntityKey from the ObjectStateManager. If the object is currently not loaded into the object context, a query is executed in an attempt to return the object from the data source.

Which actually seems to me like how it should work naturally. After all, if it’s in the context and I am positive I don’t want to update the object from changes in the database, wouldn’t it make sense to not bother with the database at all? Seems like a wasted action. Again, I am guessing that the reason it does this is that it’s an all or nothing thing. It either does it all the time (IE query no matter the reason) or never. Grey area just doesn’t seem to fit Entity Framework design in this matter.

Ok so you have this thing that you want to do and it involved stuff. And when you do that stuff you want something to happen. And when that happens you want to know that it happened and tell someone, “Hey I did stuff and something happened.” That, in essence, is what Concurrency Mode is for provided all this had to do with the Entity Framework and updating records.

Concurrency Mode allows you to make sure that nothing has changed in the database before or during the current save. Basically it checks the database to make sure the record being updated still is in the same condition it was when you loaded it to the context. If it isn’t, then an exception is thrown. (Maybe not the way I would prefer handling this situation, but hey I don’t work for the M) How do I get this to happen? Well it’s a pretty easy change, though it has an annoying short coming. First the change.

Open up the model browser (IE double click on the .edmx file) and select any property on any entity. Then view the properties.

And in the image you can see that there is a property surprisingly named “Concurrency Mode” and there are two options: Fixed and None. Guess which one I’m talking about in this post.

Now if I just left you with that, you’d have everything you need to know and you wouldn’t waste the next minute you’re going to waste on my findings. Good thing you aren’t smart enough to walk away.

You might wonder how the Entity Framework is able to do that, after all even I did and as we all know, I am genius.
Say you have a User entity where the UserName and MainEmail properties had Fixed Concurrency. Now suppose the old values of these were ‘oldValue’ and you just changed them (UI Side) to ‘HIHIHI’. At this point you want to save the changes. Well if you use profiler and a watch as it saves, you’ll see something like this:

exec sp_executesql
N'update [TIDBA].[TI_USER]
set
  [MainEmail] = @0,
  [UserName] = @1
where
  ((([UserID] = @2)
and
  ([MainEmail] = @3))
and
  ([UserName] = @4))
',N'@0 nchar(6),
@1 varchar(6),
@2 int,
@3 nchar(6),
@4 varchar(6)',
@0=N'HIHIHI',
@1='HIHIHI',
@2=1,
@3=N'oldValue',
@4='oldValue'

Which cleans up into:

update [TIDBA].[TI_USER]
set
  [MainEmail] = 'HIHIHI',
  [UserName] = 'HIHIHI'
where
  ((([UserID] = 1)
and
  ([MainEmail] = 'oldValue'))
and
  ([UserName] = 'oldValue'))

As you can see, the Entity Framework matches the originally loaded values against the database (Only on Fixed Concurrency properties) and sees if it can find a record. If it can’t, it means those values have changed. The exception follows.

Now besides the exception part (A bit much but can be handled), the only annoying thing is that you have to do this property by property. I don’t see a way yet to make it standard for the entire entity. Such is life.

So in THIS POST I talked about how querying worked on a conceptual level but turns out there is something going on that’s not expected. Remember how I said that if the object is in the context, the database won’t be hit? Turns out that isn’t true to an extent. With the help of profiler, I found outconfusing thing or two, and neither have to do with why this is was on the front page of CNN.

Say you for some reason want to select a user like so:

    SiteUser someUser = context.SiteUser.First(role => role.UserID == 1);

WOW THAT IS NEW AND AMAZING!!!117!

Right off the bat, I bet you can guess what happens. If not (You know, if you didn’t read THIS POST or you have difficulties with revolving doors), here’s the idea in script form:

            CONTEXT
    Am I having this Object?

Context looks into its bag, notices it doesn't have the object,
and then looks over at Database with confused look.

            DATABASE
   No but I gots it for you.

Database hands object to Context.  Context looks happy.

            CONTEXT
  I has a object now.

At this point, two things are obvious: I’ve lost it and Context now has a User with an UserID of one and it got this by querying the database. Now suppose for no real reason you want the same user. (Assume it is the same context and the user is already in the context.)

            CONTEXT
    Am I having this Object?

Context looks into his bag and pulls out the object.  Database walks over.

           CONTEXT
    You has object?

            DATABASE
   Yes but I gots it for you.

Database hands object to Context.  Context doesn't take it.

            CONTEXT
  I has it already.

So in this stunning display, we can see that even though the context already has the user, it still asks the database for the thing. How do I know this? Well I used profiler on both statements. What I thought would happen is on the first eh First it would hit the database, hydrate the object, and go on its merry. Second First would just grab it from the context and give it to me. Fact is, it STILL HITS THE DATABASE. Now the next possible idea is that it’s going to the database to see if the record has changed and then will update the object with any changes EXCEPT what has been changed on the object. Yeah not so much. (Although there is a way you can force that, but I’ll post that in the next post) Fact is, it really doesn’t do anything with it as long as you have the default settings. In fact, (FACT FACT FACT FACT) the default settings say that:

Objects that already exist in the object context are not loaded from the data source.
This is the default behavior for queries or when calling the Load method on an
EntityCollection<(Of <(TEntity>)>).

Seems kind of odd that by default something in the context will not be loaded if it’s already there but it still hits the database regardless. So what this default really means is that it may or may not hit the database, but any changes in the database will not be reflected in the object. It really doesn’t mean that the database won’t be queried. Seems kind of odd.

What not good enough? Fine, figure this out.

So in the first part of this life changing tutorial, I explained what lazy loading was and how it worked with the Entity Framework. Now I wanted to get into querying/loading and how it’s handled. At least at a very simplistic angle. (Simplistic is what I do best. Day-oh!) Something that has to be understood is how the Entity Framework handles the hydrating/loading of objects and how it relates to the ObjectContext. Say you have the same User/Site example from the first part and you load a site:

    using(SomeEntities context = new SomeEntities ())
    {
       //where SomeEntities  : ObjectContext
       Site neededSite = context.Site.Where(site => site.ID = siteID);
    }

Ok so what happens here? Well basically the context will check itself to see if it holding said Site and if it isn’t, it will grab it from the database. This doesn’t seem like a big deal since it’s pretty obvious it won’t have the given Site yet since the context was just created. Now let’s get a silly example going for the sake of learning.

    using(SomeEntities context = new SomeEntities ())
    {
       //where SomeEntities  : ObjectContext
       Site neededSite = context.Site.Where(site => site.ID = siteID);
       neededSite.SiteName = someString;

       if(context.Site.Any(site => siteName == neededSite.SiteName))
       {
          //return error since the name already exists in the table.
       }

    }

Now as it is, this doesn’t seem too bad right? I change the name of the site and then see if that name is taken already in the database. If these seems ok to you it just means you don’t yet understand how the Entity Framework eh… works. The first time you go to get the Site, it fills it’s Site list with the Sites from the database, then checks the list for the one you want. It will then proceed to give you a reference to the Site in that list. Next time you do a query on the Sites list, it will NOT query the database but rather it will just query the Sites list it has in memory. Next, the line:

    neededSite.SiteName = someString;

Is updating the object in that list. Now you would think (If you are used to say not caching results from database queries) that the next query:

    context.Site.Any(site => siteName == neededSite.SiteName)

Would hit the database again and check the items there and make sure that SiteName doesn’t exist. But far as Entity Framework is concerned it already loaded that list, so it doesn’t need to hit the database again. This is good to save the performance hit of getting from the database but dangerous if you don’t understand this. Fact is, the context is just going to look at it’s cached list of Sites and run that query and guess what’s in that list? The object that you just changed. So basically that query will compare the object to itself at some point as it iterates through the list and yee haw it will find a Site that already has that SiteName… the one you just changed. Solution?

You can include the SiteID in the query:

    context.Sites
            .Any(
                  site => siteName == neededSite.SiteName
                  && site.SiteID != neededSite.SiteID
                 )

This way you know that you won’t compare the same object to itself.

OR You can create a new context if you really want to get a fresh look at the database. This will allow you to have a context with the new changes and a context pre changes so you can be sure you are comparing the object to a list without it.

Now you might look down the list of methods on the context and see the Refresh method. This might be temping because you might think that a refresh would give you what you want, a completely new list to compare against. There are two ways you can handle this:

     context.Refresh(RefreshMode.Store, context.Site);

Seems like this would be good since it basically means I want to completely restore the list from the database and clear out any changes I made in the list. Oh wait, what was that? ” clear out any changes I made in the list”. This means those changes we made to the object, yeah gone. So now the:

    context.Sites
           .Any(
                  site => siteName == neededSite.SiteName
                  && site.SiteID != neededSite.SiteID
                )

Query is hosed because the neededSite.SiteName has been reverted to whatever was in the database originally.

You could try this:

     context.Refresh(RefreshMode.ClientWins, context.Site);

In which any changes made to the objects will overwrite any information being brought in from the database. Hmm, 6 of one and a half dozen of the other. This just brings us back to the same problem. The list will still contain the changes to the object and therefore we will once again compare the object to itself.

Now you might think that maybe you should just check the list BEFORE applying the changes to the object, and that would be a good way of going about it:

    using(SomeEntities context = new SomeEntities ())
    {
       //where SomeEntities  : ObjectContext
       Site neededSite = context.Site.Where(site => site.ID = siteID);

       if(context.Site.Any(site => siteName == someString))
       {
          //return error since the name already exists in the table.
       }
       else
       {
          neededSite.SiteName = someString;
          //Continue or save
       }
    }

This would work since you are checking the list before the object has been changed. This might work if you do your saving in the same layer that you have your UI, but if you do something like this:

    ...
    Site someSite = Site.GetSiteById(id);
    someSite.SiteName = txtSiteName.Text;
    someSite.Save();
    ...

In this example, I have created a Save method on the Site object that will take care of validation and saving all in one. It also means that I either am sharing a context between the GetSiteId method and the Save method, or that I am using two contexts. If it’s the latter, then it won’t matter since the context in the Save method will not “own” the current someSite object. (And therefore will not have the changes to the someSite object reflected in its list) However, if they do share the same context then you will run into the above problem.

In the next post, I’ll talk about how to share a context… which I won’t try to sell you as the right thing to do. Just something to think about.

So when I wrote this and this I didn’t really have a precursor to some of the concepts in the posts so I figured I’d get them out of the way now since I’ll be posting a bunch on the Entity Framework.

First off is lazy loading. Now I could put the obligatory stupid joke here that gives some kind of novel, but incorrect, explanation of the phrase (Like say making someone else wash your clothes) but I so much better than that. So what is lazy loading? Simply put it’s not getting something until you need it. Kind of like “If it ain’t broke then don’t fix it” instead you trade “broke” with “needed” and “fix” with “get”. So it’s really not like that phrase. It is however, when talking about objects being hydrated (ie loaded) from some kind of storage (ie database) in a program written by some idiot. (ie me) Let’s say you have a User table and a Site table in which there are many Users to one Site. Now with an ORM this will be represented by an User object, a Site object, and a property on User that is of type Site. It would look something like this:

    public class User
    {
      private Site _site = null;

      public Site ParentSite
      {
         get
         {
            if(_site == null)
            {
               _site = SomeMethodToGetSite();
            }

            return _site;
         }
      }
    }

When you load the User from storage, initially the Site object on the User object is null. This is done because if you don’t need the Site yet, why take up space until you do? This is the concept of lazy loading. Now in most ORMs, the Site would automatically be hydrated the first time the Site property was accessed. Entity Framework is slightly more complicated when dealing with the many to many or one to many situation.

This happens when you have a Users list on the Site object that basically represents every User in the User table that has the same SiteID as the Site in question. OR maybe you have a hanging table UserSite that allows for one Site to have many Users and one User to have many Sites. Either way, you will be left with a collection that represents this relationship. (Users list on the Site object) Now say you do this:

  Site someSite = LoadSiteByID(siteID);

and then you try to do this:

  someGrid.DataSource = someSite.Users;
  someGrid.DataBind();

When the page loads, there are no records in the grid. In a normal ORM this wouldn’t be a problem. It would see that you are accessing the .Users property and hit the database to fill the list. Entity Framework requires you to take one more step in this case.

  someGrid.DataSource = someSite.Users.Load();

Not that big of a deal. Now there is a check even to see if it is loaded in the first place:

  order.Users.IsLoaded

This is a boolean property that says true when it has been loaded. Next post I’ll hit on what happens when you query and how that relates to this situation.

I thought that my journey with the Entity Framework was going to be like falling off a cliff, fast and painless but instead it’s been more like driving a Hummer in a Smart car convention, just have to plow my way through.

The latest bump in my path has been the ObjectContext. Just to let it be known, I’m like most kids with a new game, I don’t bother to read the instructions and the throw the controller when I don’t have a clue what I’m doing. My use of the Entity Framework has been no different. So using “o’ skool” standards, I immediately decided that I would have some kind of Singleton context so that I didn’t keep making new ones, and assumedly new connections. Everything seemed fine and dandy until I hit this situation which brought some, at the time, unexplainable or difficult issues.

First off, say you have a Site and Role class, and Role is Many to One with Site. Now let’s say you are creating a page that is used to create/update/delete roles. On this page you have the usual section for updating or creating a new role that involves a Name for the Role and a drop down list to pick a Site from. Now on the update side of it you have basic rules: The Role must have a name, the Role must have a Site, and the Name of the Role can’t already exist in the database for the site. So a new role might look like:

  Role editRole = Role.GetByID(someID);
  editRole.RoleName = txtRoleName.Text;
  editRole.Site = GetSiteFromID(ddlSite.SelectedValue.ConvertTo<Int32>);

The GetByID is just a query that gets the Role by and ID by using the Singleton ObjectContext. (KEY POINT) Beyond that, nothing special except my awesome string convert. Yah. Now the next step before committing to the database is something like:

  if
  (
    context.Any
    (
      role => role.Name == editRole.RoleName
      && role.Site == editRole.Site
    )
  )
  {
     //return error or whatever
  }

So right there I’m checking to see if the new name for the role exists already. Now say I’m updating a role and that I KNOW the new name doesn’t exist. You would think that query would return false and life would be good. Unfortunately two things happened… and they will change your life forever. (EDIT: I realize I could also check to make sure the ID doesn’t match and should do that, but if I had done that initially I wouldn’t have figured out the next part)

First:

I got back an error saying the name already exists. Now as I said, I know it didn’t so how could that happen? Well remember that Singleton ObjectContext idea? First time I grab the Role, it doesn’t exist in the ObjectContext.Role list. So it grabs it from the database and then stores it so that every time I now run that GetByID method, I am actually getting the object from the context NOT THE PERSISTENCE LAYER. (IE database or whatever) Therefore, any changes I make are changes to the object in that context. So what does that mean? Well say I do update the RoleName on that object and then run a query to see if that name exists, guess where that query is looking? Yup the context. And since it’s looking at the same stupid object I just changed, of course the name is going to exist now. Basically I comparing the object to itself to see if it’s name exists. Ouch.

B:

After cancelling, (IE not saving) The grid that I had displaying all the Roles now has that new RoleName in it. What the f? My code specifically says NOT to save unless that (name already exists) query returns false. So how the hell has the name changed? Well just like before, the query that gets the list of Roles is no longer looking at the database but now at the Roles list in the context. That same Roles list that has the Role I changed earlier. Therefore any change to that object will now be reflected when getting the list from the context. Uhg.

So what can you learn about this? Once a list is “loaded” for the first time, it is held in the context until (presumably) you use the Refresh method on the context to refresh the given list. (Not sure how well this works). Therefore, any queries after the first will reflect all changes to the objects in the context, whether you saved to the database or not. The Singleton idea isn’t looking real great right now.

Ok so this is the situation, you’ve just stolen 50 billion dollars from various wealthy people using a ponzi scheme and you’ve been caught. What do you do? I have no idea but if that’s your situation and you’ve ended up at this site, I’m guessing your escape route has something to do with the Entity Framework and how to load a many to one relationship. Or maybe you’re just interested in the latter anyhow.

So here’s the problem I ran into the other day. I have a Site table and a Role table, and every Role record has a SiteID (Foreign Key) that the Entity Map represents in a property. (Site Class) Now, when I “hydrate” a Role, the Site property is null. This is because Entity Framework, in a round about way, supports lazy loading sort of. (Lazy Loading meaning that any relationship, be it a Site object on a Role or say a Users list on a Role, will not be loaded at the time you “hydrate” the main object and will only be loaded when the property is accessed.) So it makes sense that the Site object exposed by the property is not loaded yet because I haven’t accessed the property.

With the Entity Framework, and one to many relationships (Say a Users list on a Role object) you have to not only access the property, but you have use the Load method to make sure it gets loaded:

    from user in someRole.Users.Load()

Well with a many to one relationship (Many Roles to one Site) this is a propblem since the Site property returns only a single Site:

    someRole.Site.Load(); //Boom, not possible

Load can’t be used since Site isn’t a collection. So how the hell do I load this stupid thing? Well that has to be done in the query itself. Kind of annoying:

    context.Role.Include("Site").Where(someClause).ToList();

As you can see, in that query I had to add the Include method and the value is actually the property name. Now when I look at the list of roles returned, the roles now have the Site property loaded. As I said, kind of annoying, but there is something interesting about these relationships that I’ll post about next.