EF 4.1 - DBContext SqlQuery and Include

Question

I want to execute a raw sql using DBContext SqlQuery and then include related entites. I've tried the following but it doesn't load the related entities:

string sql = "Select * from client where id in (select id from activeclient)";
var list = DbContext.Database.SqlQuery<Client>(sql).AsQueryable().Include(c => c.Address).Include(c => c.Contactinfo).ToList();

Any help?

Answer 1

It is not possible. Include works only with ESQL or linq-to-entities because it must be processed during query building to construct correct SQL query. You cannot pass SQL query to this construction mechanism. Moreover your code will result in executing SQL query as is and trying to call Include on resulted enumeration.

You can also use simple linq query to get your result:

var query = from c in context.Clients.Include(c => c.Address).Include(c => c.Contactinfo)
            join ac in context.ActiveClients on c.Id equals ac.Id
            select c;

This should produce inner join in SQL and thus filter are non-active clients.

Answer 2

Not direct answer, but instead of writing raw sql query you could use something like this

_conext.Clients.Where(c => _conext.ActiveClients.Any(a => a.ClientId == c.Id));