Linq intersect or join to return items from one co

Question

I have the following scenario: Two lists of different Types which happen to contain 3 matching properties (in reality, the names are not the same as they are from different systems/database tables, but their contents match).

In my example I have named the properties the same just to make it easier!

I'd like to get a list of Prefix+Number+Suffix for accounts where there is a matching item in lookup (NOTE: Lookup can contain the same values multiple times - the rest of the properties in the objects are different)

This is the code I am currently using, but it feels clunky. Is there a cleaner, better way to acheive the same result? I tried "Contains()" but wasn't sure how to restrict to all three properties.

    var accounts = new List<Account>{
        new Account{Prefix="001", Number="10101", Suffix="666"},
        new Account{Prefix="001", Number="10202", Suffix="777"},
        new Account{Prefix="001", Number="10303", Suffix="777"},
        new Account{Prefix="002", Number="20101", Suffix="666"},
        new Account{Prefix="002", Number="20101", Suffix="777"}
    };

    var lookup = new List<Lookup>{
        new Lookup{Prefix="001", Number="10101", Suffix="666"},
        new Lookup{Prefix="001", Number="10101", Suffix="666"},
        new Lookup{Prefix="002", Number="20101", Suffix="666"},
        new Lookup{Prefix="001", Number="10101", Suffix="666"},
    };

    var match = ((from a in accounts
                  select a)
                 .Intersect(from l in lookup
                            from a in accounts
                            where l.Prefix == a.Prefix &&
                            l.Number == a.Number &&
                            l.Suffix == a.Suffix
                            select a)
                            ).Select(a => string.Format("{0}{1}{2}", a.Prefix, a.Number, a.Suffix));

Answer 1

You can use the following code to get the match:

var match = (from a in accounts
             select new { P = a.Prefix, N = a.Number, S = a.Suffix })
  .Intersect(from l in lookup
             select new { P = l.Prefix, N = l.Number, S = l.Suffix })
  .Select(t => string.Format("{0}{1}{2}", t.P, t.N, t.S));;

You make use here of the automatically generated equality operators on anonymous types.

Answer 2

Try this:

from a in accounts
join l in lookup on
new
{
    a.Prefix, a.Number, a. Suffix
}
equals
new
{
    l.Prefix, l.Number, l. Suffix
}
into gls
select a

Answer 3

I would not work directly with the tables but use a database view in cases like this. Create a view which performs the union for you and returns a normalised data structure, like so:

CREATE VIEW ExampleView
AS
SELECT
    Prefix = a.Prefix,
    Number = a.Number,
    Suffix = a.Suffix
FROM
    FirstTable AS a
UNION ALL
SELECT
    Prefix = l.Prefix,
    Number = l.NumberWithDifferentName,
    Suffix = l.WeirdlyNamedSuffix
FROM
    SecondTable AS l

Then you can run a simple select on that view instead of performing complex database logic in your application, where it does not really belong anyway:

SELECT Prefix, Number, Suffix FROM ExampleView; /* or obviously the LINQ equivalent */

Here a link to an article on that matter (why to use views): http://www.tdan.com/view-articles/5109. To cite the part that explains why its better practice to let the database do what it does best, not the application:

Developers find having to work with normalized data structures awkward and time-consuming, since it involves coding complex SQL queries that join data from multiple tables. [...] "refactoring" non-normalized data structures into normalized ones after the fact is always extremely difficult and labor-intensive, and sometimes isn't even possible (because data in non-key fields must be "refactored" into key fields, and data in these fields may have missing or incorrect values).

Answer 4

why dont you try join between them

from a in accounts
join l in lookup 
on
            new { a.Prefix, a.Number, a. Suffix}  
            equals
             new { l.Prefix, l.Number,l. Suffix}  
            select a;

Answer 5

Why not just simply:

        match = (from l in lookup
                 from a in accounts
                 where l.Prefix == a.Prefix &&
                 l.Number == a.Number &&
                 l.Suffix == a.Suffix
                 select string.Format("{0}|{1}|{2}", l.Prefix, l.Number, l.Suffix))
                 .Distinct();