Multiple join in Entity Framework and include all data

I want to write a query in Entity Framework that has multiple joins. The only problem is the tables arn't related using their primary / foreign keys (its the Umbraco database), as such I can't use .Include and the Navigation properties.

Essentially the query I would like to run is this:

select t.*, n.* from cmsContentType t
inner join cmsContentTypeAllowedContentType a on t.nodeId = a.Id
inner join vicinity.DocumentTypeExtendedProperty x on x.UmbracoDocumentTypeId = t.pk
inner join umbracoNode n on n.id = t.nodeId

I have two EF entities mapped to cmsContentType and umbracoNode as such I would like them to be populated as if I was running the query开发者_StackOverflow中文版 like

var q = from p in cmsContentType.Include("umbracoNode")

How can this be done

Above answers are both correct.

However, to simplify things - why not create a view?

The view could do those joins you wanted, then your LINQ query becomes as easy as pie:

var q = from x in objectContext.myFunkySpecialView
        select x;

Try the following approach:

var q = from t in objectContext.cmsContentType  
        from a in objectContext.cmsContentTypeAllowedContentType  
        from x in objectContext.DocumentTypeExtendedProperty  
        from n in objectContext.umbracoNode  
        where t.nodeId == a.Id && x.UmbracoDocumentTypeId == t.pk && n.id == t.nodeId  
        select new {  
          t = t,  
          n = n  
        };  

Hope this helps.

An Include translates the navigational properties into outer joins. In your sql example you're actually using inner joins which are easily translated into LINQ. The LINQ query that Devart posted is absolutely correct, however using the join keyword instead of nested from statements IMHO produces a query that looks almost identical to the original SQL query.

var q = from t in objectContext.cmsContentType  
        join a in objectContext.cmsContentTypeAllowedContentType on t.nodeId equals a.Id
        join x in objectContext.DocumentTypeExtendedProperty on t.pk equals x.UmbracoDocumentTypeId
        join n in objectContext.umbracoNode on t.nodeId equals n.id
        select new {  
          t = t,  
          n = n  
        };  

Try this it will surely work

var Salary = from SalaryTable in testEntity.Salary 
             from MonthTable in testEntity.Month 
             where SalaryTable.Month == MonthTable.Month1 
             select SalaryTable,

Also keep in mind that you cant return an anonymous type ie data from both the tables in a single list or var for that you have to create a class with properties you need to return and iterate through it and return the newly created class as a list to get the desired output.