LINQ - 模拟IN列中的多个列 [英] LINQ - Simulating multiple columns in IN clausule

问题描述

  SELECT * 
 FROM Tabl Tabb 
 WHERE （tabb.Col1，tabb.Col2）IN（（1,2），（3,4））
  

考虑我以下实体：

  public class Tabb 
 {
 public int Col1 {get ;组; } 
 public int Col2 {get;组; } 
 //其他道具
} 
  

和条件类

  public class Search 
 {
 public int Col1 {get;组; } 
 public int Col2 {get;组; 
} 
  

我需要写：

  public IEnumerable< Tabb>选择（IEnumerable< Search> s）
 {
 var queryable = this.context.Tabbs; 
 return queryable.Where（\ * some * \）.ToList（）; 
} 
  

如何选择实体，搜索集合包含具有相同的价值 Col1 和 Col2 ？

编辑：

  var result = from x in entity 
 join y in entity2 
在新的{x.field1，x.field2}等于new {y.field1，y.field2} 
  

它不工作（正如我预期的） - 在可能的情况下 entity2 不是一个实体表，它是静态集合，所以EF抛出异常（sth like：can not find映射层类型为Search []）;

解决方案

您希望列匹配的所有行，可能会有所帮助：

  myDBTable.Where（x => 
 myStaticCollection.Any（y => y.Col2 == x.Col2）&& 
 myStaticCollection.Any（y => y.Col1 == x.Col1））
 .ToList （）
 .Select（x => new Search {Col1 = x.Col1，Col2 = x.Col2}）; 
  

这就是说，我希望每一行都有任何 Col2 在我的静态集合中匹配此数据库 Col2 AND其中任何 Col1 匹配此数据库 Col1

  this.context.Searches.Join（
 this.context.Tabbs， 
s => s.Col2，
t => t.Col2，
（search，tab）=> new {
 search，
 tab 
}）; 
  

这将带回 IEnumerable<'a> 包含搜索和选项卡

这个人正在做类似的事情 LINK

  var result = from x in entity 
 join y in entity2 
 on new {x.field1，x.field2}等于new {y.field1，y.field2} 
  

一旦你有你的结果，那么你想列举，以确保你打了数据库并获取所有的值。一旦他们在记忆中，那么你可以将它们投射到对象中。

  result.ToList（）。选择（a => ; new MyEntity {MyProperty = a.Property}）; 
  

In oracle I can do the following query:

SELECT *
FROM Tabl Tabb
WHERE (tabb.Col1, tabb.Col2) IN ( (1,2), (3,4))

Consider I 've following entity:

public class Tabb
{
   public int Col1 {get; set; }
   public int Col2 {get; set; }
   // other props
}

and criteria class

public class Search
{
   public int Col1 {get; set; }
   public int Col2 {get; set; }
}

I need to write:

public IEnumerable<Tabb> Select(IEnumerable<Search> s)
{
   var queryable = this.context.Tabbs;
   return queryable.Where(\* some *\).ToList();
}

How can I select entities, that search collection contain instance of search that has the same value of Col1 and Col2?

EDIT:

var result = from x in entity
             join y in entity2
             on new { x.field1, x.field2 } equals new { y.field1, y.field2 }

It doesn't work (As I expected) - in may case entity2 is not a entity table, it is static collection, so EF throws exception (sth like: cannot find mapping layer to type Search[]);

解决方案

Just understood the problem better. You want all rows where the columns match, may be this will help:

myDBTable.Where(x => 
     myStaticCollection.Any(y => y.Col2 == x.Col2) && 
     myStaticCollection.Any(y => y.Col1 == x.Col1))
     .ToList()
     .Select(x => new Search { Col1 = x.Col1, Col2 = x.Col2 });

This is saying, I want each row where any Col2 in my static collection matches this database Col2 AND where any Col1 matches this database Col1

this.context.Searches.Join(
     this.context.Tabbs,
     s => s.Col2,
     t => t.Col2,
     (search, tab) => new {
          search,
          tab
     });

This will bring back IEnumerable<'a> containing a search and a tab

This guy is doing something similar LINK

var result = from x in entity
             join y in entity2
             on new { x.field1, x.field2 } equals new { y.field1, y.field2 }

Once you have your result then you want to enumerate that to make sure you're hitting the database and getting all your values back. Once they're in memory, then you can project them into objects.

result.ToList().Select(a => new MyEntity { MyProperty = a.Property });

这篇关于LINQ - 模拟IN列中的多个列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！