SQL Linq intersect with multiple columns

Question

I have 2 Lists

A = 74137 records (7 columns)
B = 63029 records (5 columns)

and want to find the all the records that have

A.column1 = B.column1 and A.column2 = B.column2 and A.column3 = B.column3

but want to return the entire 7 columns of A. so far I tried this code:

var ListCucolumn3uriPostale = (from A in _TempListaAdreseInProcesare.AsParallel()
                                           join B in tblcolumn3uri.AsParallel()
                                           on
                                           new { column1 = A.column1.ToUpper(), column2 = A.column2.ToUpper(), column3 = A.column3 }
                                           equals
                                           new { column1 = B.column1.ToUpper(), column2 = B.column2.ToUpper(), column3 = B.column3 }
                                           select new TempSpecificatii
                                            {
                                                column0 = A.column0,
                                                column4 = A.column4,
                                                column1 = B.column1,
                                                column2 = B.column2,
                                                column3 = B.column3,
                                                column5 = B.column5,
                                                column6 = B.column4,
                                                column7 = A.column7,
                                                column8 = A.column8,
                                                column9 = A.column9,
                                                column10 = A.column10
                                            }).ToList();

but I got a List of 186395 records, that is not normal. I changed the code like this:

var listacucoduripostale = (from A in _TempListaAdreseInProcesare.AsParallel()
                            select new
                            {
                                column1 = A.column1.ToUpper(), column2 = A.column2.ToUpper(), column3 = A.column3
                            }).Intersect(
                           from B in tblCoduri.AsParallel()
                           select new
                           {
                               column1 = B.column1.ToUpper(), column2 = B.column2.ToUpper(), column3 = B.column3
                           }).ToList();

I got the good results of 23567 records but had only 3 Columns in the Lists, I want all the columns from A. What I must change to get the desire result. Thanks.

Jodrell · Accepted Answer · 2012-09-28 09:11:12Z

1

I'd be tempted to do it like this, the HashSet should provide a fast look up.

var bLookupSet = new HashSet(b.Select(b =>
                   new { 
                       Column1 = b.Column1, 
                       Column2 = b.Column2,
                       Column3 = b.Column3
                       }));

var resultList = a.Where(a => bLookupSet.Contains(
                   new { 
                       Column1 = a.Column1, 
                       Column2 = a.Column2,
                       Column3 = a.Column3
                       })).ToList();

If you need the match to be case insensitive, and all 3 columns are strings, then I'd do,

var bLookupSet = new HashSet(b.Select(b =>
                   new { 
                       Column1 = b.Column1.ToLower(), 
                       Column2 = b.Column2.ToLower(),
                       Column3 = b.Column3.ToLower()
                       }));

var resultList = a.Where(a => bLookupSet.Contains(
                   new { 
                       Column1 = a.Column1.ToLower(), 
                       Column2 = a.Column2.ToLower(),
                       Column3 = a.Column3.ToLower()
                       })).ToList();

edited Sep 28, 2012 at 9:11

answered Sep 28, 2012 at 9:04

Jodrell

35.8k6 gold badges94 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jodrell Over a year ago

@XandrUu depends how you want the halve them.

Collectives™ on Stack Overflow

SQL Linq intersect with multiple columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related