c# - Entity Framework 中的联盟

Question

我有两个表:Vehicles 和 Workers。

Vehicle(Id, Number)
Workers(Id, Name, ContractorVehicleNumber)

我想编写 lambda 查询以返回所有车辆和承包商车辆。像在 sql 中的东西:

SELECT Id, Number
FROM Vehicle
UNION
SELECT NULL, ContractorVehicleNumber
FROM Workers

这是我做的:

public IQueryable<Vehicle> Get(bool includeContractorVehicles)
{
    IQueryable<Vehicle> query = GetQuery();

    if (includeContractorVehicles == true)
    {
        WorkerRepository rep = new WorkerRepository();
        IQueryable<Vehicle> contractorsVehicles = rep.GetWirkers().
            Select(x => new Vehicle()
            {
                VehicleNumber = x.ContractorVehicleNumber
            });
        query = query.Union(contractorsVehicles);
    }

    return query;
}  

但我得到一个异常(exception):

The entity or complex type 'XXXXXXXX' cannot be constructed in a LINQ to Entities query.

Answer 1

您不能在投影中构造映射实体类型。仅当您创建用于投影的新特殊类型时，您之前的示例才会起作用:

public class VehicleResult
{
    public string Number { get; set; }
    ... // If you don't need more then one column you can use simple type instead of custom class
}

您的方法将如下所示:

public IQueryable<VehicleResult> Get(bool includeContractorVehicles)
{
    IQueryable<VehicleResult> query = GetQuery().Select(v => new VehicleResult { ... });

    if (includeContractorVehicles == true)
    {
        WorkerRepository rep = new WorkerRepository();
        IQueryable<VehicleResult> contractorsVehicles = rep.GetWorkers().
            Select(x => new VehicleResult()
            {
                Number = x.ContractorVehicleNumber
            });
        query = query.Union(contractorsVehicles);
    }

    return query;
}