Entity Framework Nested Projection Involving Collection

One of the nice thing we like about Linq to Entity here in Palador is the ability to form the IQueryable to ensure performance - by minimizing amount of data traveling across the wire.

We have the following data model:

In one repository method, we need to get a given Persona, AboutMe field which is in auxilary table - the list of its PersonaLink (read that as : “Things I Like” that list my favorite urls), along with it the value of the LinkBitmapId.

We do not need nor want to grab PersonaProfile.Photo.

We also do not want to use this syntax to get to the PersonaLink.LinkBitmapId - without making extra round-trips (using lazy loading, it would be one for each record in PersonaLink property) and/or grab the varbinary field LinkBitmap.Bitmap:

– which is quite ugly.

So, we would need to do EF Projection - and yes, we know Include will not work – we don’t want it anyway, since we want also to do projection on the property (LinkBitmap) of the collection property (PersonaLink) of the Persona.

So, after spending some time scouring the Internet and tinkering with Linq to Entity – we get the following to work:

var dbPersona = (from p in context.Persona where p.PersonaID == personaId select new { p.PersonaID, p.NickName, p.ContributorRank, p.EmailAddress, p.PersonaProfile.AboutMe, p.DefaultPartner, p.DefaultGeo, PersonaLink = (from pl in p.PersonaLink select new { pl.Description, pl.Url, pl.LinkBitmap.LinkBitmapId}) }).FirstOrDefault();

After getting back the data I want in a single trip to database in dbPersona - we could then simply ‘unwrap’ this dbPersona instance (anonymous type) and put the data we want into a new instance of Persona and return that thing.

We also obtained the generated SQL and examine it. It turns out to be not too bad – a whole bunch of joins and no crazy subquery.

We hope this might help or trigger some ideas in exploring Linq to Entity to do things which we wished we could do in EF (in particular for achieving more optimum performance).

One thing we found in scouring the Internet is that ObjectQuery method – are rather limited compared to Linq to Entity. It seems that there is no way to do nested projection (involving collection) using ObjectQuery method in EF.