0

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.

Jonathan Wood
  • 65,341
  • 71
  • 269
  • 466
  • It's not possible for us to know just from the code you've posted whether that's being evaluated in SQL or in memory. – Ben Aaronson May 23 '14 at 00:13
  • Well, I believe I've established that it is not being evaluated in SQL. I can provide additional information if there are questions. But I'm really trying to find out the rules here that determine if it is evaluated in SQL, and if there's a way to override that. – Jonathan Wood May 23 '14 at 00:14

2 Answers2

1

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);
AD.Net
  • 13,352
  • 2
  • 28
  • 47
1

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);
Community
  • 1
  • 1
Martin Booth
  • 8,485
  • 31
  • 31