Union IQueryable and List

Question

This is my code:

var query = context.SomeEntities.Select(e => new SomeDto
{
    Name = e.Title,       
})

The variable query is IQueryable.

var list = new List<SomeDto>
{
    new SomeDto
    {
          Name = "SomeName1"      
    },
    new SomeDto
    {
          Name = "SomeName2"      
    }
};

Now I need union these collections,I write

query = query.Union(list)

But in this place I get exception

Unable to create a constant value of type 'SomeDto'. Only primitive types or enumeration types are supported in this context

I know that I can do ToList() for first collection,but I need union collections without call to database.How can I do this?

[Refer this two link][1] [1]: http://stackoverflow.com/questions/25632591/concat-iqueryable-collections-in-one-db-request [1]: http://stackoverflow.com/questions/6614989/how-to-combine-2different-iqueryable-list-collection-with-same-base-class-linq hope this will help you — user3825493, Nov 11 '14 at 06:57
@user3825493,these questions about union two IQueryable collections,but in my case one collection IQueryable ,another List — vborutenko, Nov 11 '14 at 07:05

score 1 · Answer 1 · edited May 23 '17 at 10:26

As user3825493 pointed, there are answers regarding similar issues. The first link pretty much explains the whole thing.

Your problem is that due to the fact that Union is mapped to SQL UNION, arguments of the operation are to be converted to data types acceptable by Entity Framework. However, the only types acceptable by it for items inside IEnumerable input parameters are, as specified in the error you get, primitive types and enum's.

You should either:

var unionOfQueryWithList = query.AsEnumerable().Union(list)

or, if you like to use the SQL facilities, write a stored procedure which performs the union as part of its logic and find the way to pass your data into SQL, such as this.

The former loads all the results of the query into a memory before performing the union. It is convenient for the cases when you have all the pre-filtered data returned from EF and only want to use the output for later processing in your code.

The later takes you out of the EF zone and required for the cases when you have operations which will benefit from running on SQL engine, such as filtering or joining DB data based on a complex data you have access to in your code.

score 0 · Answer 2 · answered Nov 11 '14 at 07:53

0

either you convert the query to IEnumerable or you can try this :

list.Union(query); // use Union() of list not query :)
it will work and you still don't need to get data from DB you can reverse the order if you want :)

answered Nov 11 '14 at 07:53

noor saif

224
1
7

Union IQueryable and List

2 Answers2