Making thing more succint using linq to entities for inner joins

Question

Any way to make this less verbose?

var model =
(
    from MvrTable in
    LinqEntitiesCtx.Mvrs

    join MvrMedsTable in LinqEntitiesCtx.MvrMeds
    .Where(Id => Id.FKMvrId == 1)//inner join will be fast with this!

    on MvrTable.PKMvrId equals MvrMedsTable.FKMvrId

    join MvrLocationTable in LinqEntitiesCtx.MvrLocations
    on MvrTable.PKMvrId equals MvrLocationTable.FKMvrId

    join MvrEmployeeTable in LinqEntitiesCtx.MvrEmployees
    on MvrTable.PKMvrId equals MvrEmployeeTable.FKMvrId

    //notice i am using a different primary key that previouslly
    join MvrMedsAdminRouteTable in LinqEntitiesCtx.MvrMedsAdminRoutes
    on MvrMedsTable.PKMvrMedsId equals MvrMedsAdminRouteTable.FKMvrMedsId

    select new
    { //here I choose the columns I want to display
       MvrTable.PKMvrId,
       MvrTable.VarianceDescription,
       MvrTable.CaseNumber,
       MvrTable.DateOfReport,
       MvrTable.DateOfVariance
    }
);

Equivalent SQL code of above:

SELECT [t0].[PKMvrId], [t0].[VarianceDescription], [t0].[CaseNumber], [t0].[DateOfReport], [t0].[DateOfVariance], [t1].[PKMvrMedsId]
FROM [Mvrs] AS [t0]
INNER JOIN [MvrMeds] AS [t1] ON ([t0].[PKMvrId]) = [t1].[FKMvrId]
INNER JOIN [MvrLocations] AS [t2] ON ([t0].[PKMvrId]) = [t2].[FKMvrId]
INNER JOIN [MvrEmployees] AS [t3] ON [t0].[PKMvrId] = [t3].[FKMvrId]
INNER JOIN [MvrMedsAdminRoutes] AS [t4] ON ([t1].[PKMvrMedsId]) = [t4].[FKMvrMedsId]
WHERE [t1].[FKMvrId] =ParamMvrId

I am so sorry that was misleading.. Assume I am getting data from each of those tables. — hidden, Nov 27 '11 at 22:26

score 1 · Accepted Answer · answered Nov 21 '11 at 22:08

By using Associations it could probably be written more compact. Something like (not complete):

var model =  from MvrTable in LinqEntitiesCtx.Mvrs
             where MvrTable.MvrMeds.MvrLocations.Any() //These are the Associations
             select new
             {
               MvrTable.PKMvrId,
               MvrTable.VarianceDescription,
               MvrTable.CaseNumber,
               MvrTable.DateOfReport,
               MvrTable.DateOfVariance
             };

You don'y really need the joins since you are not getting any data from those tables. You should use Any instead which corresponds to SQL's EXISTS.

score 0 · Answer 2 · answered Nov 21 '11 at 22:07

I believe changing the join's to from's will make it more clear. You could also abbreviate your entity alias's

var model =
(
    from MvrTable in LinqEntitiesCtx.Mvrs
    from MvrMedsTable in LinqEntitiesCtx.MvrMeds
                                        .Where(Id => Id.FKMvrId == 1)
                                        .Where(x => MvrTable.PKMvrId == x.FKMvrId)
    from MvrLocationTable in LinqEntitiesCtx.MvrLocations
                                            .Where(x => MvrTable.PKMvrId  == x.FKMvrId)
    from MvrEmployeeTable in LinqEntitiesCtx.MvrEmployees
                                            .Where(x => MvrTable.PKMvrId == x.FKMvrId)  
    from MvrMedsAdminRouteTable in LinqEntitiesCtx.MvrMedsAdminRoutes
                                                  .Where(x => MvrMedsTable.PKMvrMedsId == x.FKMvrMedsId)
    select new
    { 
       MvrTable.PKMvrId,
       MvrTable.VarianceDescription,
       MvrTable.CaseNumber,
       MvrTable.DateOfReport,
       MvrTable.DateOfVariance
    }
);

Making thing more succint using linq to entities for inner joins

2 Answers2