CODEWITHSUNDEEP
c#.netsql-serverentity-frameworklinq
Andrei
Andrei

Reputation: 44600

IQueryable Union, Unable to create a null constant value of type 'System.Collections.Generic.IEnumerable`1[[System.Int32

I am trying to implement LINQ query to Entity Framework with union. Let's say I have 2 tables: Users and TempUsers. I try to:

var users = context.Users
        .Select(u => new UserModel 
        {
            Name = u.Name,
            Roles = u.Roles.Select(r => r.Id)
        })
    .Union(context.TempUsers
        .Select(u => new UserModel 
        {
            Name = u.Name,
            Roles = null
        }))
.ToList();

Model class:

public class UserModel
{
    public string Name { get; set; }
    public IEnumerable<int> Roles { get; set; }
}

I am getting NotSupportedException with message:

Unable to create a null constant value of type 'System.Collections.Generic.IEnumerable`1[[System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]'. Only entity types, enumeration types or primitive types are supported in this context.

If I remove Roles = null, I get NotSupportedException with message:

The type 'UserModel' appears in two structurally incompatible initializations within a single LINQ to Entities query. A type can be initialized in two places in the same query, but only if the same properties are set in both places and those properties are set in the same order.

An ideas how to fix it? Thanks!

Upvotes: 1

Views: 2229

Answers (2)

Ivan Stoev
Ivan Stoev

Reputation: 205599

The first exception cannot be avoided, but the second can with the following trick.

Since EF complains that you initialize one and the same type differently, you can create and initialize a different type derived from the original one.

For instance:

public class TempUserModel : UserModel { }

and then

var users = context.Users
        .Select(u => new UserModel 
        {
            Name = u.Name,
            Roles = u.Roles.Select(r => r.Id)
        })
    .Union(context.TempUsers
        .Select(u => new TempUserModel 
        {
            Name = u.Name
        }))
    .ToList();

UPDATE: The above trick works for single value properties but not for collection type properties. And more importantly, it turns out that Concat / Union with nested subqueries are not supported at all. So the only way to do that in SQL is to perform the Concat (which btw is the equivalent of the SQL UNION ALL) master tables first, and then do conditional joins, but I'm not sure it does worth the effort. Better use some of the mixed approaches from the currently accepted answer.

Upvotes: 0

ocuenca
ocuenca

Reputation: 39326

To avoid those exceptions, you can do the union in memory calling AsEnumerable extension method:

var users = context.Users
        .Select(u => new UserModel 
        {
            Name = u.Name,
            Roles = u.Roles.Select(r => r.Id)
        })
        .AsEnumerable() //Add this 
        .Union(context.TempUsers
        .Select(u => new UserModel 
        {
            Name = u.Name,
        }))
.ToList();

The problem is your are trying to project with a DTO that have a complex type as a property. That doesn't let you assign a default value because EF only supports entity types, enumeration types or primitive types when you try to project a query. Also not worth use an anonymous type because both projections must be based in an anonymous type with the same property names, so, at the end is the same problem.

Update

Now that I saw a solution using Inheritance, this idea came to my mind.

public TempUser
{
  public string Name { get; set; }
} 

public UserModel:TempUser
{
  public IEnumerable<int> Roles { get; set; }
}

Then to try execute union in your server side, you will also need to call OfType extension method

var query=context.TempUsers
          .Select(u => new TempUser
          {
            Name = u.Name,
          })
          .Union(context.Users
          .Select(u => new UserModel 
          {
            Name = u.Name,
            Roles = u.Roles.Select(r => r.Id)
          }).OfType<TempUser>())
          .ToList();

The downside of this solution if the result will be a List<TempUser>

Upvotes: 2

Related Questions