Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Union in entity framework

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.

like image 217
Naor Avatar asked Jun 26 '11 11:06

Naor


People also ask

What is Union in Linq?

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.

How do I create a Union in C#?

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().

What does a Union all do?

The UNION ALL command combines the result set of two or more SELECT statements (allows duplicate values).


2 Answers

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;
}  
like image 104
Ladislav Mrnka Avatar answered Sep 27 '22 21:09

Ladislav Mrnka


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 ?

like image 34
Magnus Avatar answered Sep 27 '22 23:09

Magnus