Union in entity framework

Question

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.

Ladislav Mrnka · Accepted Answer · 2018-04-09 01:42:42Z

21

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;
}

edited Apr 9, 2018 at 1:42

user1143634

answered Jun 26, 2011 at 13:18

Ladislav Mrnka

365k60 gold badges666 silver badges673 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Magnus · Accepted Answer · 2011-06-26 13:15:57Z

5

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 ?

edited Jun 26, 2011 at 13:15

answered Jun 26, 2011 at 12:56

Magnus

47.3k8 gold badges88 silver badges128 bronze badges

2 Comments

Naor Over a year ago

I need the whole Vehicle object. What is the different between Union and Concat?

Magnus Over a year ago

Concat returns the complete result of both queries, Union removes the duplicates.

Collectives™ on Stack Overflow

Union in entity framework

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related