I tried to Union IQueryable and IEnumerable but got this error

Union(__p_2)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'.

and tried many different writing methods but it doesn't work. So can IEnumerable and IQueryable be Union?

My data looks like the following:

var users = new List<Person>().AsQueryable();
var user = GetUsersByXXX(XXX, YYY);   // return IQueryable<Person>
var userExtra = GetUsersExtraByXXX(XXX, YYY); // return IEnumerable<Person>

if (userExtra.Any())
{
  users = user.Union(userExtra);
}    

return await users.AsNoTracking().OrderBy(x => x.NAME ).ToListAsync();         

class

public class Person
{
    public string CODE { get; set; }
    public string NAME { get; set; }
    public string PHONE { get; set; }
}

CodePudding user response：

You must understand those two things are two different concepts:

• An IQueryable is an interface that "Provides functionality to evaluate queries against a specific data source"
• An IEnumerable is an interface that "Exposes the enumerator, which supports a simple iteration over a collection".

An IQueryable can be turned into IEnumerable by calling AsEnumerable().

Note, that an IQueryable can be a non-evaluated expression. I.e. the data has not been retrieved from the database. By calling something like ToList or AsEnumerable, you enumerate, thus evaluate it. The normal (synrchonous) methods will block until that's finished. That's why there are also ToListAsync and AsAsyncEnumerable.

CodePudding user response：

The IQueryable<T> interface already implements IEnumerable<T>, you just need to cast to IEnumerable<T> to ensure you use the linq-to-objects implementation.

var users = GetUsersByXXX(XXX, YYY).AsEnumerable() // "cast" it
    .Union(GetUsersExtraByXXX(XXX, YYY));

The added check to see if the "extra" users is empty is unnecessary. If it's already empty, the union would essentially be a noop.

CodePudding user response：

The error message is caused by an error where the query you're generating cannot be translated into SQL. It would appear that IQueryable<T>.Union() cannot be translated to SQL and the error is thrown.

You would need to work around this by either calling one of the methods mentioned in the error to force the union to be performed in .NET by having Entity Framework retrieve the full set of data for GetUsersByXXX() first and then performs the union in memory.

var users = new List<T>().AsQueryable();
var user = GetUsersByXXX(XXX, YYY);   // return IQueryable<T>
var userExtra = GetUsersExtraByXXX(XXX, YYY); // return IEnumerable<T>

if (userExtra.Any())
{
  users = user.AsEnumerable().Union(userExtra);
}