c# - SQL Linq 与多列相交

Question

我有 2 个列表

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

并想找到所有有

的记录

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

但想返回 A 的全部 7 列。到目前为止，我试过这段代码:

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();

但是我得到了一个包含 186395 条记录的列表，那是不正常的。我像这样更改了代码:

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();

我得到了 23567 条记录的良好结果，但列表中只有 3 列，我想要 A 中的所有列。我必须更改什么才能获得所需的结果。谢谢。

Answer 1

我很想这样做，HashSet 应该提供快速查找。

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();

---

如果您需要匹配不区分大小写，并且所有 3 列都是字符串，那么我会这样做，

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();