LINQ's .Union() with unknown data types

Question

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?

If you're doing a query against two DBs with EF I would assume you need to do the Union in memory. So slap an `AsEnumerable` before the `Union` and another at the end of the query inside of the `Union`. — juharr, Jun 02 '16 at 12:44
make sure `solution1` or `solution2` is not null itself.... for example skip them in this way `(from solution1 in db1.Solutions where (solution1 != null) select new .....` — M.kazem Akhgary, Jun 02 '16 at 12:46
Just doing `null AS [ExpirationDate]` may not infer the correct type, so CAST or CONVERT null as datetime may do the trick (within the view)? — Brian Mains, Jun 02 '16 at 12:48
@M.kazemAkhgary my `solution1` is actually `null`. I wonder why, but atleast I found that issue. — Detilium, Jun 02 '16 at 12:56
if having null inside your collection is expected then no worry that you can ommit them with `where` . but if it is not expected to have null item inside your collection dont just ommit them with `where`. try to find the main problem that is causing null elements... — M.kazem Akhgary, Jun 02 '16 at 13:00
also you must know that Union works with hash codes... so Union will not work correctly if for example two objects are equal but with a difference in `ExpirationDate` they will not produce same hash codes. which is most likely your case. so my suggestion is to not include `ExpirationDate` in comparisons at all — M.kazem Akhgary, Jun 02 '16 at 13:03
See https://stackoverflow.com/questions/4278993/is-it-possible-to-perform-joins-across-different-databases-using-linq — sgmoore, Jun 02 '16 at 13:08
@sgmoore Then we're back to client side. I'm aware that I didn't mention this, but I need everything to work server side — Detilium, Jun 02 '16 at 13:10
@sgmoore And I cannot make a view that joins these together as this is Microsoft Azure DB's, and that version doesn't support cross database querying — Detilium, Jun 02 '16 at 13:22
If cross-db queries are not supported, how do you ever want to implement your requirement of server-side cross-db queries? — usr, Jun 02 '16 at 13:35

score 3 · Answer 1 · answered Jun 02 '16 at 12:52

3

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.

answered Jun 02 '16 at 12:52

usr

168,620
35
240
369

but I cannot use the `AsEnumerable()` . check my return type of the function – Detilium Jun 02 '16 at 12:54
@Detilium You'll have to change the return type to `IEnumerable` and really you should use a custom class to project your values into so you can use something like `IEnumerable` for strong typing. – juharr Jun 02 '16 at 12:56
The code I have shown you is only a simple example of action, this is nothing compared to my actual code. I NEED this to be IQueryable, and I will not change the return type. – Detilium Jun 02 '16 at 12:59
You cannot query across contexts, period. Maybe a fake queryable is all you need? Call `AsQueryable()` on the end result. It's hard to recommend a specific solution without knowing what you want to achieve and why you have these restrictions. – usr Jun 02 '16 at 13:04

Edgars Pivovarenoks · Answer 2 · 2016-06-02T13:23:22.680

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;}

    }

}

I tried to recreate your case (see updated answer). Wondering myself what we are missing. — Edgars Pivovarenoks, Jun 02 '16 at 13:24

valex · Answer 3 · 2016-06-02T12:57:09.727

0

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

edited Jun 02 '16 at 12:57

answered Jun 02 '16 at 12:50

valex

23,966
7
43
60

1

You shouldn't mix ADO.Net's `DBNull` with a Linq SQL provider. It should be doing the tranlation between `DBNull` and `null` for you. – juharr Jun 02 '16 at 12:52
@juharr that won't work anyways, as `System.DBNull.Value` doesn't correspond to the first select statement – Detilium Jun 02 '16 at 12:52
This answer would most likely work, as the type is now recognized as being a `Datetime?`. My problem though is that my entries are `null`, throwing the `NullReferenceException`. My problem differs from the topic of this question. – Detilium Jun 02 '16 at 13:07

score 0 · Answer 4 · answered Jun 02 '16 at 13:35

0

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.

answered Jun 02 '16 at 13:35

Detilium

2,868
9
30
65

You should add this information to the question. But it's immaterial since your requirement cannot be satisfied by this approach. You'll just get another different error, as you said. – usr Jun 02 '16 at 13:35

LINQ's .Union() with unknown data types

4 Answers4