CODEWITHSUNDEEP
c#sql-serverlinq
Detilium
Detilium

Reputation: 3034

LINQ's .Union() with unknown data types

I need to get data from to different databases and then combine these two data sets into a single IQueryable collection.

Database #1 example of data:

-- SOLUTION #1
[ID] [int],
[Name] [nvachar](50),
[ExpirationDate] [datetime],
[Users] [int]

Databse #2 example of data:

-- SOLUTOIN #1
[ID] [int],
[Name] [nvarchar](50),
[Users] [int]

As you can see, the solution from database #2 doesn't have the attribute ExpirationDate, which gives me some problems when doing the Union().

I have tried the following:

public static IQueryable GetAll()
{
    var list = (from solution1 in db1.Solutions
            select new
            {
                Id = solution1.ID,
                Name = solution1.Name,
                ExpirationDate = solution1.ExpirationDate,
                Users = solution1.Users
            })
        .Union(from solution2 in db2.Solutions
            select new{
                Id = solution2.ID,
                Name = solution2.Name,
                ExpirationDate = (DateTime?) null,
                Users = solution2.Users        
            });
}

But unfortunately, this doesn't work. When calling this, I get the following exception:

Object reference not set to an instance of an object.

I suppose that this is because I set the ExpirationDate = (DateTime?) null.

I have also tried to get the data from SQL views, like so (this is an example of db2 as db1 is kind off self explanatory)

CREATE VIEW [dbo].[v_Solutions]
    AS SELECT s.ID,
        s.Name,
        null AS [ExpirationDate],
        s.Users
    FROM Solution

An then altered the LINQ select new statement to the following:

.Union(from solution2 in db2.Solutions
            select new{
                Id = solution2.ID,
                Name = solution2.Name,
                ExpirationDate = (DateTime?) solution2.ExpirationDate,
                Users = solution2.Users        
            });

But doing this gives me a compile error:

Cannot convert type 'int?' to 'System.DateTime?'

Though the data in solution2.ExpirationDate should just be null.

I'm not quite sure how to complete this statement. Any ideas?

Upvotes: 2

Views: 673

Answers (4)

Detilium
Detilium

Reputation: 3034

I completely messed up. My instances of db1 and db2 were null all along. Something in my code wasn't working though it should. I have another problem now, but the NullReferenceException is gone.

Upvotes: 0

Edgars Pivovarenoks
Edgars Pivovarenoks

Reputation: 1684

Try to modify 1st query this way :

// ...
ExpirationDate = (DateTime?)solution1.ExpirationDate,

UPDATE:

I tried to recreate your case, this seems to work :

using System.Collections.Generic;
using System.Threading.Tasks;
using System.Data.Entity;
using System.Linq;  
using System;

public class Program
{
    public static void Main()
    {
        var itemsA = new[]{new ItemA {Name = "a", Date = DateTime.Now}};
        var itemsB = new[]{new ItemB {Name = "b" }};


        var list = (from solution1 in itemsA
            select new
            {
                Name = solution1.Name,
                Date = (DateTime?)solution1.Date
            })
        .Union(from solution2 in itemsB
            select new{
                Name = solution2.Name,
                Date = (DateTime?)null                
            });

        Console.WriteLine(list.Count());

    }
    public class ItemA
    {
        public string Name {get;set;}
        public DateTime Date {get;set;}
    }
    public class ItemB
    {
        public string Name {get;set;}

    }

}

Upvotes: 0

valex
valex

Reputation: 24144

In the second case try:

CREATE VIEW [dbo].[v_Solutions]
    AS SELECT s.ID,
        s.Name,
        CAST(NULL as DateTime)  AS [ExpirationDate],
        s.Users
    FROM Solution

Upvotes: 0

usr
usr

Reputation: 171178

LINQ 2 SQL and EF do not support queries pulling from multiple DB contexts. Apparently, you are even triggering a bug doing that causing a NullReferenceException.

You can perform the union in memory:

db1.SomeTable.AsEnumerable().Union(db2.SomeTable.AsEnumerable())

If you want the union executed in the database you need to use raw SQL, or map some table to another database in the DBML or use table valued functions or views.

Upvotes: 3

Related Questions