Entity Framework include vs where

My database structure is this: an OptiUser belongs to multiple UserGroups through the IdentityMap table, which is a matching table (many to many) with some additional properties attached to it. Each UserGroup has multiple OptiDashboards.

I have a GUID string which identifies a particular user (wlid开发者_StackOverflow社区 in this code). I want to get an IEnumerable of all of the OptiDashboards for the user identified by wlid.

Which of these two Linq-to-Entities queries is the most efficient? Do they run the same way on the back-end?

Also, can I shorten option 2's Include statements to just .Include("IdentityMaps.UserGroup.OptiDashboards")?

using (OptiEntities db = new OptiEntities())
{
    // option 1
    IEnumerable<OptiDashboard> dashboards = db.OptiDashboards
        .Where(d => d.UserGroups
            .Any(u => u.IdentityMaps
                .Any(i => i.OptiUser.WinLiveIDToken == wlid)));

    // option 2
    OptiUser user = db.OptiUsers
        .Include("IdentityMaps")
        .Include("IdentityMaps.UserGroup")
        .Include("IdentityMaps.UserGroup.OptiDashboards")
        .Where(r => r.WinLiveIDToken == wlid).FirstOrDefault();

    // then I would get the dashboards through user.IdentityMaps.UserGroup.OptiDashboards
    // (through foreach loops...)
}

---

You may be misunderstanding what the Include function actually does. Option 1 is purely a query syntax which has no effect on what is returned by the entity framework. Option 2, with the Include function instructs the entity framework to Eagerly Fetch the related rows from the database when returns the results of the query.

So option 1 will result in some joins, but the "select" part of the query will be restricted to the OptiDashboards table.

Option 2 will result in joins as well, but in this case it will be returning the results from all the included tables, which obviously is going to introduce more of a performance hit. But at the same time, the results will include all the related entities you need, avoiding the [possible] need for more round-trips to the database.

---

I think the Include will render as joins an you will the able to access the data from those tables in you user object (Eager Loading the properties).

The Any query will render as exists and not load the user object with info from the other tables.

For best performance if you don't need the additional info use the Any query

---

As has already been pointed out, the first option would almost certainly perform better, simply because it would be retrieving less information. Besides that, I wanted to point out that you could also write the query this way:

var dashboards =
    from u in db.OptiUsers where u.WinLiveIDToken == wlid
    from im in u.IdentityMaps
    from d in im.UserGroup.OptiDashboards
    select d;

I would expect the above to perform similarly to the first option, but you may (or may not) prefer the above form.