Linq intersect or join to return items from one collection that have matching properties to another?
I have the following sce开发者_如何学Cnario: 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));
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.
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();
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
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).
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;
精彩评论