I have two tables: Vehicles and Workers.
Vehicle(Id, Number)
Workers(Id, Name, ContractorVehicleNumber)
I would like to write lambda query to return all the vehicles and the contractor vehicles. Something like in sql:
SELECT Id, Number
FROM Vehicle
UNION
SELECT NULL, ContractorVehicleNumber
FROM Workers
This is what I made:
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;
}
But I get an exception:
The entity or complex type 'XXXXXXXX' cannot be constructed in a LINQ to Entities query.
LINQ Union is an extension method that requires two collections to combine the two collections and returns the distinct elements from both collections. LINQ Union supports only method syntax; it does not support the query syntax. The Union method presents in both the classes Queryable class and Enumerable class.
The Union operator is Not Supported in C# & VB.Net Query syntax. However, you can use Union method on query variable or wrap whole query into brackets and then call Union().
The UNION ALL command combines the result set of two or more SELECT statements (allows duplicate values).
You cannot construct mapped entity type in projection. Your former example will work only if you create a new special type used for projection:
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
}
And your method will look like:
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;
}
You cant create entities in the select statement. Try this instead:
public class VehicleDTO
{
public int Id { get; set; }
public int Number { get; set; }
}
public IQueryable<VehicleDTO> Get(bool includeContractorVehicles)
{
var query = GetQuery().Select(x => new VehicleDTO(){ ID = c.ID, Number = c.Number });
if (includeContractorVehicles)
{
WorkerRepository rep = new WorkerRepository();
var contractorsVehicles = rep.GetWirkers().
Select(x => new VehicleDTO(){ Number = x.ContractorVehicleNumber});
query = query.Union(contractorsVehicles);
}
return query;
}
Also are you sure you want a Union
and not a Concat
?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With