Get Records Matched on Multiple Fields From Tables in Separate Databases

Question

I'm doing this with LINQ, but could possibly entertain creating a stored procedure for this. I have a working solution that retrieves both record lists and then filters with .Join() in memory. I am looking for an improvement to this or a correction to my failed use of .Any() so I don't need to load the full list of records for both first.

Situation:

I have two tables that are in different DBs (so no key/navigation prop relationships and I can't change this :/).

Table 1 is a list of Foos.

Foo Entity Properties: ID, ..., prop1, prop2, prop3, ...

Table 2 is a collection of Bars that match a unique combination of three properties found in a Foo. There is a unique key for (prop1, prop2, prop3). I didn't use a composite primary key since this is part of a web app and that would complicate navigation.

Bar Entity Properties: ID, prop1, prop2, prop3, ...

I need to retrieve all Bars that match a combo from the active Foos.

I've looked into potential solutions that use .Any() / .Exists(), but they have not worked.

//This doesn't work
var foos = db1.Foos.Where(d => d.IsActive)
                   .Select(c => new 
                   {
                       c.prop1,
                       c.prop2,
                       c.prop3,
                   })
                   .ToList();

var fooBars = db2.Bars.Where(b => foos.Any(f => f == new { b.prop1,
                                                           b.prop2,
                                                           b.prop3 }))
                      .ToList();

My working solution: (see below as Answer)

I don't think that there is a way to combine this into one query since the entities are in different DbContexts, but is there a better way to retrieve filtered results without getting the entire list of bars first?

score 0 · Answer 1 · answered Sep 24 '16 at 01:20

My current working solution:

var foos = db1.Foos.Where(d => d.IsActive)
                   .Select(c => new 
                   {
                       c.prop1,
                       c.prop2,
                       c.prop3,
                   })
                   .ToList();

var bars = db2.Bars.ToList();

var fooBars = bars.Join(foos,
                        x => new { x.prop1, x.prop2, x.prop3 },
                        y => y,
                        (x, y) => x)
                  .ToList();

This is not ideal as it loads every Bar (3k+ records) when I only will be looking for the ~200 Bars that match the prop combos of my active Foos.

If anyone has a better solution, I'll accept that, but this works for now.

score 0 · Accepted Answer · edited May 23 '17 at 11:48

0

In case you only need to load entities. You can create a view in db2 that links to tables in db1. The view will be available in your db2 context.

TSQL: Create a view that accesses multiple databases

edited May 23 '17 at 11:48

Community

1
1

answered Sep 24 '16 at 07:30

Pleun

8,856
2
30
50

I ended up implementing a view for the join. I can give the view a self-describing name, but this method doesn't have the benefit of explicitly self-documented logic that a LINQ query in the code has. – Jecoms Oct 12 '16 at 15:56

Get Records Matched on Multiple Fields From Tables in Separate Databases

2 Answers2