How to use INCLUDE in Linq for selected columns only?

Question

I am having the scenario with multiple linq include methods with table object associations. Suppose the scenario is:

User has Groups
User has Permissions
User has Vehicles

var _users=
(from u in dbconetxt.Users
join g in dbconetxt.Gropus on u.userId equals g.userId
join p in dbconetxt.Permissions on u.userId equals p.userId
join v in dbconetxt.Vehicles on u.userId equals v.userId
Where u.Status=true
select u).Include(u.Groups)
         .Include(u.Permissions)
         .Include(u.Vehicles)
         .ToList()

After joining all these tables within a single query, I select the User Object. Certainly, I would get List, but I want each User Object should Include its respective Groups, Permissions, Vehicles, but from Vehicles and Permissions, I want to load only few Columns/Properties, not all. So, how do I specify which Columns to load in this scenario?

I am using Entity Framework 4.1 + C# + SQL Server.

Solution 1

Include is an extension method attached to IQueryable. That means you have to use it on the DbSet Users.

If you want to select only specified columns, you can create an anonymous type or a class with a parameterless constructor.

var users = from u in dbContext.Users.Include(u => u.Groups)
            where u.Status == true
            select new 
            {
                u.Name,
                u.Id,
                u.WhatSoEver
            };

or

var users = from u in dbContext.Users.Include(u => u.Groups)
            where u.Status == true
            select new UserView
            {
                Name = u.Name,
                Id = u.Id,
                Property1 = u.WhatSoEver
            };

Solution 2

If you want subset of columns you must use projection to anonymous or custom type or create specific database view for your query and map it to new read only entity. Include will always load all columns of included entity (so other approach is dividing the entity with table splitting but that looks like overkill). Also Include has very limited usage - shape of the root query mustn't change so once you use any custom projection or join Include will not work.

EF is ORM - once you map entity you will always work with whole entity not only its part. If you need to work with part of the entity you must use projection to non entity class (non mapped).