CODEWITHSUNDEEP
c#.netlinqentity-framework
Jonathan Wood
Jonathan Wood

Reputation: 67175

Entity Framework Query: What's Happening Under the Covers Here?

I have the following code.

var category = Office.Categories.FirstOrDefault(c => c.Description == name);

Office is a Framework Entity object that was read earlier.

Even though my database uses case-insensitive string comparisons, and one of the items matches exactly except for the case, this code is returning null.

My understanding of what is happening here is that Office.Categories is returning all related rows, and then those rows are being searched by regular LINQ, which is case-sensitive.

If so, that is horribly inefficient as I only want to return the row that matches my condition.

Can anyone confirm my understanding of this? And is it possible to force the filtering to take place in SQL so that I don't need to return the rows I'm not interested in? (And the text comparison will be case-insensitive?)

Thanks for any help.

Upvotes: 0

Views: 101

Answers (2)

Martin Booth
Martin Booth

Reputation: 8595

Correct, so you can't:

  • Call the Categories property getter if using lazy loading because this causes EF to retrieve the full list of categories from the database

  • Use eager loading, because this also causes the full list of categories to be loaded from the database

To avoid this, you can use the following code (from this answer here: Using CreateSourceQuery in CTP4 Code First)

    public ObjectQuery<T> CreateNavigationSourceQuery<T>(object entity, string navigationProperty)
    {
        var ose = this.ObjectContext.ObjectStateManager.GetObjectStateEntry(entity);
        var rm = this.ObjectContext.ObjectStateManager.GetRelationshipManager(entity);

        var entityType = (EntityType)ose.EntitySet.ElementType;
        var navigation = entityType.NavigationProperties[navigationProperty];

        var relatedEnd = rm.GetRelatedEnd(navigation.RelationshipType.FullName, navigation.ToEndMember.Name);

        return ((dynamic)relatedEnd).CreateSourceQuery();
    }

Add it to your context and call it to create an ObjectQuery.. you can call FirstOrDefault on this and it won't retrieve all entities from the database:

var category = context.CreateNavigationSourceQuery<Category>(Office, "Categories").FirstOrDefault(c => c.Description == name);

Upvotes: 1

AD.Net
AD.Net

Reputation: 13399

If you already have the Office object loaded (also depends on lazy loading, but assuming you're outside the context) then you're basically doing a linq-to-object query which is case sensitive. If you want to do linq-to-sql then you should do it differently, something like:

context.Offices.FirstOrDefault(o=>o.OfficeId == someId)
                  .Categories.FirstOrDefault(c => c.Description == name);

or 

context.Categories.FirstOrDefault(c => c.OfficeId == Office.OfficeId 
                                    && c.Description == name);

Upvotes: 1

Related Questions