CODEWITHSUNDEEP
c#.netsql-serverentity-frameworklinq
Matt
Matt

Reputation: 27001

LINQ: Why does a union of a union throw error message "IQueryable<...> does not contain a definition for 'Union'"?

The question is about nesting unions in LINQ: I want to combine data from tables in 3 different databases and show them in one result table.

To make it clear what I want to achieve, I am showing how that would be done in SQL (I am using the Northwind, Nutshell and Pubs databases so it is easier for you to try it out by yourself):

SELECT 'Territories' as [Table], TerritoryDescription as Item, RegionID as ID 
  FROM Northwind.dbo.Territories
UNION
SELECT 'MedicalArticles' as [Table], Topic as Item, ID as ID  
  FROM Nutshell.dbo.MedicalArticles
UNION
SELECT 'Authors' as [Table], City as Item, Zip as ID 
  FROM pubs.dbo.Authors

The underlying table structure is: table structure This query works fine without errors and returns a table with 3 columns (Table, Item and ID), combining the data from the 3 tables Territories, MedicalArticles and Authors:

Result

And if you add a top 3 to each of the 3 select statements, you'll get:

ResultTop3

Now I've tried the same in LINQ by writing code like:

void Main()
{
    // In LinqPad:
    // Drag + Drop databases from Schema explorer to code window with pressed Ctrl key
    var dc = this; // database context: Northwind + Nutshell + pubs

    // first database Northwind is default
    var q1 = (from s in dc.Territories select new { Table = "Territories", 
                Item = s.TerritoryDescription, ID = s.RegionID }).Distinct().Take(5); 

    // second database .Nutshell needs to be referenced
    var q2 = (from s in dc.Nutshell.MedicalArticles select new { Table="MedicalArticles", 
                Item = s.Topic, ID=s.ID }).Distinct().Take(5); 

    // third database .Pubs needs to be referenced
    var q3 = (from s in dc.Pubs.Authors select new { Table = "Authors", 
                Item = s.City, ID = s.Zip }).Distinct().Take(5); 

    // union q1 with q2 works
    var u1 = q1.Union(q2.Select(s => s)); u1.Dump();

    // but union u1 with q3 does not work
    //var u2 = u1.Union(q3.Select(s => s)); u2.Dump();      
}

The first union (q1 with q2) works fine - but I can't apply the 3rd query q3 to the result (i.e. u1.Union(q3.Select(s => s)) doesn't work).

The error message I am getting when I uncomment the line querying u2 isn't very helpful:

CS1929 'IQueryable<<anonymous type: string Table, string Item, int ID>>' does not contain a definition for 'Union' and the best extension method overload 'ParallelEnumerable.Union<<anonymous type: string Table, string Item, string ID>>(ParallelQuery<<anonymous type: string Table, string Item, string ID>>, IEnumerable<<anonymous type: string Table, string Item, string ID>>)' requires a receiver of type 'ParallelQuery<<anonymous type: string Table, string Item, string ID>>'

How can the error be fixed?

Note: The examples above can be tried out in LinqPad. Simply place the code in a window and then add the 3 databases Northwind, NutShell and Pubs by holding the Ctrl key and then drag+drop the database to the query window.

Upvotes: 4

Views: 1083

Answers (1)

Matt
Matt

Reputation: 27001

Many thanks to all who contributed above in the comments! I created this answer from the comments from Ivan and sgmoore:

"Most likely the type of the ID property in the anonymous type projections is different. Check the type of RegionID and Zip." - Ivan

"The error message shows that Zip is a string whereas RegionID is an int. A workaround would be to change ID = s.RegionID to ID = s.RegionID.ToString() and ID=s.ID to ID=s.ID.ToString()." - sgmoore

That is definitely the solution. As it seems, type checking in C# is stricter than in SQL (recap, in SQL the union worked without any additional typecasting).

So the changes are:

// first database Northwind is default
var q1=(from s in dc.Territories select new { Table = "Territories", 
            Item = s.TerritoryDescription, ID = s.RegionID.ToString()  })
        .Distinct().Take(5); 

// second database .Nutshell needs to be referenced
var q2=(from s in dc.Nutshell.MedicalArticles select new { Table="MedicalArticles", 
            Item = s.Topic, ID=s.ID.ToString()  })
        .Distinct().Take(5); 

// third database .Pubs needs to be referenced
var q3=(from s in dc.Pubs.Authors select new { Table = "Authors", 
            Item = s.City, ID = s.Zip.ToString()  })
        .Distinct().Take(5);

With that, you can do:

var u1 = q1.Union(q2.Select(s => s)); u1.Dump();
var u2 = u1.Union(q3.Select(s => s)); u2.Dump();

But the error message is definitely misleading ("IQueryable<<anonymous type: string Table, string Item, int ID>>' does not contain a definition for 'Union'"), which is why the question was asked. A more meaningful error message would be helpful here.

Upvotes: 5

Related Questions