Linq左连接与实体框架

本文关键字：实体 框架 连接 Linq | 更新日期: 2023-09-27 18:16:56

我有一个车辆表，我需要在VehicleAttribute上做一个左连接。

var vehicle = (from v in context.Vehicles
                               //join vehicleAttributes
                               join va in context.VehicleAttributes on v.VehicleId equals va.VehicleId into vAttributes
                               from vehicleAttributes in vAttributes.DefaultIfEmpty()
                               where v.VehicleId == vehicleId
                               select new { v, vehicleAttributes });

到目前为止一切顺利。VehicleAttribute也有一个列AttributeId。我只需要加入这些车辆属性，当它在这个列表:

List<Guid> allowedAttributes = (from ua in context.UserAttributes
                                                    where ua.UserId == UserSession.CurrentUser.UserId
                                                    select ua.AttributeId).ToList();

我该怎么做呢?我认为子查询可能是正确的方法，但我正在努力…

谢谢大家的回答。

编辑:用不同的方式来解释我的问题:我有这两个查询 
SELECT Vehicle.VehicleId,VehicleAttribute.AttributeId
FROM Vehicle
LEFT JOIN VehicleAttribute
ON Vehicle.VehicleId = VehicleAttribute.VehicleId
SELECT UserAttribute.AttributeId
FROM UserAttribute
WHERE UserAttribute.UserId = '4D0F8AD2-7A4D-4E29-A6D3-E5FCD6075388'

，并希望将它们组合起来，以便只获得第二个查询中的属性id。where子句不起作用，因为即使没有attributeid

Linq左连接与实体框架

，我仍然需要vehicleId

定义了allowedAttributes之后，您可以更改

vAttributes.DefaultIfEmpty()

的第一个查询:

vAttributes
    .Where(va => allowedAttributes.Contains(va.AttributeId))
    .DefaultIfEmpty()

不确定这是您的场景。您可以尝试使用子查询左联接:

//define the query that returns allowed VehicleAttributes
allowedAttributesQuery =from ua in context.UserAttributes
                          where ua.UserId == UserSession.CurrentUser.UserId
                          select ua.VehicleAttribute;  //I suppose that VehicleAttribute navigation property exists in the UserAttribute 
//query that joins Vehicles with the allowedAttributes subquery
var vehicle = (from v in context.Vehicles
                           join va in allowedAttributesQuery on v.VehicleId equals va.VehicleId into vAttributes
                           from vehicleAttributes in vAttributes.DefaultIfEmpty()
                           where v.VehicleId == vehicleId
                           select new { v, vehicleAttributes });
相关文章：