1

I have a query here that fetches a single entity object and only one nested entity that meets a specific condition, but I'm receiving an error when executing it.

Here is the query

Profile profile = dbContext.Profiles.Where(i => i.ApplicationUserGuid == guiId)
               .Include(i => i.ProfileImages.Where(k => k.IsMainImage == true)).First();

And here is the exception error message

The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties. Parameter name: path

I also tried running it with another .First(), but still same error message

Profile profile = dbContext.Profiles.Where(i => i.ApplicationUserGuid == guiId)
              .Include(i => i.ProfileImages.Where(k => k.IsMainImage == true).First()).First();
chuckd
  • 13,460
  • 29
  • 152
  • 331

1 Answers1

2

You can't filter related entities inside an Include, you are going to need to project your query with the expected result or use explicit loading:

Profile profile = dbContext.Profiles
                           .Where(i => i.ApplicationUserGuid == guiId)
                           .First();
dbContext.Entry(profile) //Explicit Loading
         .Collection(b => b.ProfileImages) 
         .Query() 
         .Where(k => k.IsMainImage == true).Take(1)
         .Load();

If you do the projection it will be only one round trip to your database, and if you use explicit loading it will be two.

Just a FYI, in case you think to project the result, project to an anonymous type or onto a DTO. More info here.

Community
  • 1
  • 1
ocuenca
  • 38,548
  • 11
  • 89
  • 102
  • I'm following your explicit loading example and its working but it's returning all the nested entities and not just the one that meets the where condition! – chuckd Nov 18 '16 at 21:57
  • I have edited my answer, `Take` method should fix that – ocuenca Nov 18 '16 at 21:58
  • I'm still a little unsure why all images are being returned when all except 1 meet the where condition? Then what's the purpose of the where condition? If it's not working? And I tried Take(1) and it still returns all the images! – chuckd Nov 18 '16 at 22:00
  • Oh, just one meet the condition? Are you completely sure? Did you check your db? – ocuenca Nov 18 '16 at 22:01
  • yes I'm sure I'm looking at the property IsMainImage now and only 1 row has that value of true – chuckd Nov 18 '16 at 22:02
  • It's not a big deal because I guess I can filter later on, but it seems like something isn't correct if the where condition isn't working/filtering properly?! – chuckd Nov 18 '16 at 22:04
  • that's weird my friend, `Where` method should control that – ocuenca Nov 18 '16 at 22:05
  • oh I know why, it's because the first call returns all images in profile! But I'm still confused, shouldn't the explicit call after filter out everything that's unwanted? – chuckd Nov 18 '16 at 22:06
  • Or shouldn't the first call lazy load everything, because it's not, it's loading all nested entities! – chuckd Nov 18 '16 at 22:07
  • it must be because lazy loading is enabled and you are accessing to that navigation property some how in your code. My advice is disable lazy loading, check the first link that I quoted – ocuenca Nov 18 '16 at 22:10
  • you mean I'm enabling eager loading? – chuckd Nov 18 '16 at 22:12
  • Did you remove the `Include` call from your query, right? – ocuenca Nov 18 '16 at 22:12
  • It's enabled by default,https://msdn.microsoft.com/en-us/data/jj574232.aspx – ocuenca Nov 18 '16 at 22:13
  • yes, I removed the include, my two queries look just like yours – chuckd Nov 18 '16 at 22:15
  • ok, I fixed it! I set dbContext.Configuration.LazyLoadingEnabled = false; above the queries – chuckd Nov 18 '16 at 22:16
  • That was what I was suggesting to do, another thing that you can do is remove the `virtual` keyword from your navigation properties, that way your entity doesn't meet the conditions to create a proxy class that handles lazy loading and tracking changes – ocuenca Nov 18 '16 at 22:18
  • just to clarify, if I remove the virtual keyword, then no images will be loaded and I can remove the dbContext.Configuration.LazyLoadingEnabled = false; statement? – chuckd Nov 18 '16 at 22:20
  • so then I guess the question I have to ask myself, from an architectual perspective, is do I want to load all related entities when fetching a profile? Because I have other nested entities, not just images! – chuckd Nov 18 '16 at 22:25
  • 1
    What I always do is disable lazy loading and use eager loading instead when I need some nav prop, but maybe your design works better with lazy loading. That property is being loaded because it has been accessed in some point in your code. Projecting your queries to ViewModel classes is a good practice, you will find good tutorials if your search a little. – ocuenca Nov 18 '16 at 22:33