Reading several tables from one single Entity Framework ExecuteStoreQuery request.

I have a library which uses EF4 for accessing a SQL Server data store. For different reasons, I have to use SQL Server specific syntax to read data from the store (for free text search), so I have to create the SQL code by hand and send it through the ExecuteStoreQuery method.

This works fine, except that the query uses joins to request several tables aside the main one (the main one being the one I specify as the targ开发者_运维百科et entity set when calling ExecuteStoreQuery), and EF never fills up the main entity's relationship properties with the other table's data.

Is there anything special to do to fill up these relationships? Using other EF methods or using special table names in the query or something?

Thanks for your help.

Executing direct SQL follows very simple rule: It uses column from the result set to fill the property with the same name in materialized entity. I think I read somewhere that this works only with the the main entity you materialize (entity type defined in ExecuteStoreQuery = no relations) but I can't find it now. I did several tests and it really doesn't populate any relation.

Ok so I'll write here what I ended up doing, which does not looks like a perfect solution, but it does not seem that there is any perfect solution in this case.

As Ladislav pointed out, the ExecuteStoreQuery (as well as the other "custom query" method, Translate) only maps the column of the entity you specify, leaving all the other columns aside. Therefore I had to load the dependencies separately, like this :

// Execute    
IEnumerable<MainEntity> result = context.ExecuteStoreQuery<MainEntity>(strQuery, "MainEntities", MergeOption.AppendOnly, someParams).ToArray();

// Load relations, first method
foreach (MainEntity e in result)
{
    if (!e.Relation1Reference.IsLoaded)
        e.Relation1Reference.Load();
    if (!e.Relation2Reference.IsLoaded)
        e.Relation2Reference.Load();
    // ...
}

// Load relations, second method
// The main entity contains a navigation property pointing 
// to a record in the OtherEntity entity
foreach(OtherEntity e in context.OtherEntities)
    context.OtherEntities.Attach(e);

There. I think these two techniques have to be chosen depending on the number and size of generated requests. The first technique will generate a one-record request for every required side record, but no unnessecary record will be loaded. The second technique uses less requests (one per table) but retrieves all the records so it uses more memory.