1

I have two log tables in seperate SQL Server databases (on different servers). The two tables have exactly the same schema. In my .NET application I can create two Linq to SQL class collections (DBML files) and then do my own custom Linq queries to union the two tables and return an anonymous type. But I have several queries (all slightly different) on the same tables and doing the unions every time is repetitive.

Is it possible to union the two tables into a single concrete/named type IEnumerable? Basically what I'd like to do is something like:

var logs = Logs.Where(l => l.TimeStart >= DateTime.Today)

And have Logs actually return data from two tables and have sensible queries sent to the two database servers no matter what I put in the .Where().

Is this possible?

Tom Hunter
  • 5,195
  • 8
  • 47
  • 74
  • Can you show us the code of your union? – Cédric Bignon Feb 02 '13 at 19:00
  • I'm actually using the FullOuterJoin extension method from [this](http://stackoverflow.com/a/13503860/62072) SO answer. – Tom Hunter Feb 02 '13 at 19:02
  • 1
    @TomHunter Have you tried creating a separate class and have the union being returned as an object as part of the class new()? That way in your code you just create a new instance of the class ( and the union etc is done as part of the new() sub) – twoleggedhorse Feb 02 '13 at 19:04
  • @TomHunter Hope that makes some sort of sense... – twoleggedhorse Feb 02 '13 at 19:05
  • I think it's actually an IQueryable that I need. I need sensible queries sent to the database no matter what kind of filtering I use in my `Where()`. – Tom Hunter Feb 02 '13 at 19:07
  • @TomHunter In that case, you could create a class which defines your IQueryable and then you can make your queries types of that class – twoleggedhorse Feb 02 '13 at 19:08

1 Answers1

1

I believe that out of the box you cannot get one IQueryable covering multiple datacontexts. You mention you have

seperate SQL Server databases (on different servers).

A solution might be to create a linked view. Example: Create View using Linked Server db in SQL Server

Once you have the linked view, the query will be fairly simple and straigtforward.

Community
  • 1
  • 1
Pleun
  • 8,836
  • 1
  • 28
  • 46