加入表，收集EntityFramework [英] Join table with collection EntityFramework

问题描述

通常我想从Employee表中检索与List相关的所有数据。键入MyEmployee包含我用于与EmployeeID映射的EntitySourceID。所以，我想检索在列表集合中与EntitySourceID匹配的EmployeeID的所有员工。

键入MyEmployee看起来像：

  public class MyEmployee 
 {
 public long PersonID {get;组; } 
 public string ConnectionString {get;组; } 
 public long EntitySourceID {get;组; } 
 public int EntitySourceTypeID {get;组; 
} 
  

我的查询看起来像：

  internal IEnumerable< Person> GetPersons（List< MyEmployee> myEmployees）
 {
 return（from p in _context.Employee 
 join pList in myEmployees on p.EmployeeID equals pList.EntitySourceID 
 select new Person 
 {
 PersonID = pList.PersonID，
 FirstName = p.FirstName，
 LastName = p.LastName，
 Name = p.Name，
后缀= p .Suffix，
 Title = p.Title 
}）。ToList（）; 
} 
  

您可以在查询中看到，当我创建新的Person对象时，我使用了pList。来自List myEmployees集合的PersonID填充Person的。

所以，我的问题是如何从Employee表中有效地检索与List集合匹配的数据，并使用pList。 PersonID（从集合）创建返回结果？

我使用EF 6，数据库第一种方法。

我没有提到此查询产生以下异常：
无法创建类型MyEmployee的常量值。

解决方案

IQueryable vs IEnumerable

解决一些更深层问题的一个好的开始是花费一些时间发现

• IQueryable 和 IEnumerable< ; T>

也可能在

• 常规代理，如 Func< T> 和表达式，如 表达式< T>

beca

现在回到你的问题

对于初学者让我们列举一些事情：

• 让我们调用RAM托管的集合MyEmployee 列表


• 让我们调用数据库表（最可能称为Employee） THE TABLE

遗憾的是，您在编写问题时没有指定一些非常重要的细节。
这让我提出4个不同的答案。
答案将根据以下2个问题的真值进行分类：

• 是列表<强大>巨大？


• THE TABLE 巨大？

我们有4个非常不同的情况：

1. 否，否


2. 否，是


3. 是，否


4. 是，是

你可以想象现在，第四个可能是最丑的。

当列表不是很大时

在1和2你可以从另一个角度考虑你的问题：

说你需要从...中获取一个（或零）记录数据库基于精确的1 参数，它是一个ID。您是否应该执行 JOIN ？

答案是：绝对不。
看看这段代码：

  var query =从_context.Employee中的员工
其中员工.EmployeeId == 23 
选择员工; 
 var found = query.FirstOrDefault（）; 
  

如果我想获取与精确的2 参数相关的记录，该怎么办？
我可以以类似的方式实现：

  var query =从_context.Employee中的员工
 where employee.EmployeeId == 23 || employee.EmployeeId == 24 
选择员工; 
 var results = query.ToArray（）; 
 
 if（results.Length == 0）
 //没有找到任何可能存在的记录
 
 else if（results.Length == 1 ）{
 if（results [0] .EmployeeId == 23）
 //然后我们发现23 
 else 
 //另一个
 
} else if（results.Length == 2）
 //发现两者，看里面看看哪个是
  

为了避免额外的混乱，我有意地以愚蠢的方式写出算法的最后一点（ if part）。

这将是一个更人性化的方法来完成：

  ... 
 var results = ...得到它们（见上文）
 
 var map = results.ToDictionary（keySelector：x => x.EmployeeId）; 
 var count = map.Count; //这给你的结果数量，和results.Length一样
 var has23 = map.ContainsKey（23）; //这告诉你是否设法获取某个id 
 var record23 = map [23]; //这实际上给你记录
 foreach（var key in map.Keys）{..} //将遍历获取的ids 
 foreach（var record in map.Values）{..} //将遍历获取的值
  

不担心 ToDictionary 扩展方法。
它与EntityFramework（通过点击它来查找）> >

现在..回到我们的故事：如果你想带来与15个ids相关联的记录怎么办？
停止。这是怎么回事？我要求您为每个可能的ids数量编码一个不同的查询？

当然不是。
只要ids的数量相对较小（意思是您被某人允许或由您自己以该请求大小来轰击数据库），您可以很好地使用列IN列表的参数SQL构造。

如何指示LINQ to SQL或EF将SQL转换为x IN y操作而不是x = y操作通过使用相应类型的原始数组和包含方法。
换句话说，得到一个负载：

  var query =从_context.Employee中的员工
其中listOfIds.Contains（employee.EmployeeId）
选择employee; 
 var results = query.ToArray（）; 
  

但是您需要列表ID，而不是MyEmployee实例列表。
你可以很容易地把它关掉：

 列表< MyEmployee> originalList = new List&MyEmployee>（）; 
 // ...说你以某种方式填充，或者你从其他地方收到了
 
 int [] listOfIds =（从originalList中的员工
选择employee.EntityId） .ToArray（）; 
 
 // ..然后继续使用EF查询
  

请注意对集合的查询清单为 IEnumerable< T> 实例，而不是 IQueryable< T> 实例，与EF或LINQ to SQL或任何其他数据库或外部数据服务无关。

如果表不显着

然后，您可以避免使用复杂查询的EF，仅将其用于全表提取，将结果临时存储在.NET进程中并定期使用LINQ，但是你喜欢。

这个故事的关键是从一开始就获取整个表格。
在你的问题你写道：

  return（from p in _context.Employee 
 join pList in myEmployees在p.EmployeeID等于pList.EntitySourceID 
选择新的人
 {
 PersonID = pList.PersonID，
 FirstName = p.FirstName 
 ... etc 
  

只需加注：

 code> var entityList = _context.Employee.ToArray（）; 
 
 return（from p in entityList //请注意此更改也是
在p.EmployeeID等于myEmployees中加入pList pList.EntitySourceID 
选择... 
  

将其加载

您可以：

• 指示数据库进行工作，但在这种情况下，您不能在流程中发送花哨的.NET实例


• 在.NET中执行工作，楼上，

Eith一方或另一方（数据库或.NET进程）需要拥有所有卡（需要具有对方的克隆）才能执行 JOIN 。 p>

所以这只是一个妥协的游戏。

关于剩余的情况 p>

如果表和列表是巨大的，那么你是**** d。
不，我只是在开玩笑。

没有人听说有人要求别人在实际上无法做到奇迹。

如果是这样的，那么你必须将问题简化成大量较小的问题。
我建议转换成一个表巨大 + 列表不是很大的问题乘以N。

那么你该怎么做？

 列表< MyEmployee>原来= ... 
 //你拿你的列表
 //，你把它分割在..的部分..说50（在我的书中不是一个数据库
 //虽然不大要小心 - 对于每个选择，数据库上的压力将几乎是50个选择并行运行）
 
 //你如何拆分？ 
 //你可以尝试这个
 
 public static IEnumerable< List&MyEmployee>> Split（List< MyEmployee> source，int sectionLength）{
列表< MyEmployee> buffer = new List< MyEmployee>（）; 
 foreach（来源的var employee）{
 buffer.Add（employee）; 
 if（buffer.Count == sectionLength）{
 yield return buffer.ToList（）; //确认您的.ToList（）缓冲区以克隆它
 buffer.Clear（）; //或其他所有结果部分实际上将指向同一个实例，它将被清除并重新填充
} 
} 
 if（buffer.Count> 0）//和if你有一个余额你也需要
 yield return buffer; //除了最后一次你真的不需要克隆它
} 
 
列表< MyEmployee>> sections = Split（original，50）.ToList（）; 
 
 //现在你可以使用这些部分
 //就像你在CASE 2（列表不是很大，但是表是）
 //里面foreach循环
 
列表< Person> results = new List< Person>（）; //准备累积结果
 
 foreach（部分的var部分）{
 
 int [] ids =（从x中的x中选择x.EntityID）.ToArray（）; 
 
 var query =从_context.Employee中的员工
其中ids.Contains（employee.EmployeeId）
 ...等; 
 
 var currentBatch = query.ToArray（）; 
 
 results.AddRange（currentBatch）; 
 
} 
  

现在你可以说这只是一种愚弄数据库相信它几乎没有任何工作要做，实际上我们仍然在大量的工作中淹没它，也许使其他并发客户端的生活变得更加困难。

嗯，是啊，但至少你可以减压。
您可以在部分之间 Thread.Sleep ...您可以使用 iterators （查找它们）和实际上并没有把RAM记录下来，这些记录需要很长时间才能处理，而是流。

你对这种情况有更多的控制。

祝你好运！

Maybe is it duplicate but I can't find proper way to do following correctly.

Generally I want to retrieve all data from Employee table which are related with List. Type MyEmployee contains EntitySourceID which I use to map with EmployeeID. So, I want to retrieve all Employees which have match EmployeeID with EntitySourceID in List collection.

Type MyEmployee looks like:

public class MyEmployee 
    {
        public long PersonID { get; set; }
        public string ConnectionString { get; set; }
        public long EntitySourceID { get; set; }
        public int EntitySourceTypeID { get; set; }
    }

My query looks like:

internal IEnumerable<Person> GetPersons(List<MyEmployee> myEmployees)
    {
            return (from p in _context.Employee
                join pList in myEmployees on p.EmployeeID equals pList.EntitySourceID
                select new Person 
                {
                    PersonID = pList.PersonID,
                    FirstName = p.FirstName,
                    LastName = p.LastName,
                    Name = p.Name,
                    Suffix = p.Suffix,
                    Title = p.Title
                }).ToList();
    }

You can see in query, when I create new Person object I used pList.PersonID from List myEmployees collection to fill Person's.

So, my question is how I can efficient retrieve data from Employee table which have match with List collection, and also use pList.PersonID (from collection) to create return result?

I use EF 6, database first approach.

Also, I didn't mention. This query produce following exception: Unable to create a constant value of type 'MyEmployee'. Only primitive types or enumeration types are supported in this context.

解决方案

IQueryable vs IEnumerable

A good start for solving some of your deeper questions would be spending some time discovering the differences between

• IQueryable andIEnumerable<T>

and maybe also between

• "Regular delegates" such as Func<T> and "Expressions" such as Expression<T>

because while they have similar forms they differ in purpose and behavior.

Now back to your question

For starters let's name a few things:

• Let's call the RAM hosted collection of MyEmployee instances THE LIST
• Let's call the database table (most probably entitled "Employee(s)") THE TABLE

Sadly you didn't specify a few very important details while writing your question. That leads me to proposing 4 different answers. The answers will be categorized based on the truth values of the following 2 questions:

• Is THE LIST huge?
• Is THE TABLE huge?

We have 4 very different cases:

1. No, No
2. No, Yes
3. Yes, No
4. Yes, Yes

You can imagine by now that the fourth is maybe the ugliest.

When THE LIST is not huge

In cases 1 and 2 you could think about your problem from a different perspective:

Say you need to fetch ONE (or zero) record(s) from the database based on Precisely 1 parameter which is an ID. Should you be performing a JOIN?

The answer is: absolutely NOT. Take a look at this code:

var query = from employee in _context.Employee
            where employee.EmployeeId == 23
            select employee;
var found = query.FirstOrDefault();

What if I wanted to fetch the records associated with Precisely 2 parameters? I could achieve that in a similar manner:

var query = from employee in _context.Employee
            where employee.EmployeeId == 23 || employee.EmployeeId == 24
            select employee;
var results = query.ToArray();

if (results.Length == 0)
   // didn't find anyone of the presumably existing records

else if (results.Length == 1) {
   if (results[0].EmployeeId == 23)
      // then we found the 23
   else
      // the other one

} else if (results.Length == 2)
   // found both, look inside to see which is which

I have intentionally written the finishing touches of the algorithms (the if part) in a silly manner in order to avoid extra confusion.

This would be a more humane approach for the finishing touches:

...
var results = ... got them (see above)

var map = results.ToDictionary(keySelector: x => x.EmployeeId);
var count = map.Count; // this gives you the number of results, same as results.Length
var have23 = map.ContainsKey(23); // this tells you whether you managed to fetch a certain id
var record23 = map[23]; // this actually gives you the record
foreach (var key in map.Keys) { .. } // will iterate over the fetched ids
foreach (var record in map.Values) { .. } // will iterate over the fetched values

Worry not about the ToDictionary extension method. It has NOTHING to do with EntityFramework (look it up by clicking on it).

Now.. back to our story: What if you wanted to bring the records associated with 15 ids? Stop. Where is this going? Am I asking you to hardcode a different query for each possible number of ids?

Of course not. As long as the number of ids is "relatively small" (meaning you are allowed by someone, or by yourself to bombard the database with that request magnitude) you could very well use an "column IN list of params" SQL construct.

How can you instruct LINQ to SQL, or EF to translate into an "x IN y" operation instead of an "x = y" operation, on the SQL side?

By using a primitive array of the respective type and the Contains method. In other words, get a load of:

var query = from employee in _context.Employee
            where listOfIds.Contains( employee.EmployeeId )
            select employee;
var results = query.ToArray();

But you need a "list of Ids" not a "list of MyEmployee instances". You could pull that off very easily like so:

List<MyEmployee> originalList = new List<MyEmployee>();
// ... say you populate this somehow, or you've received it from elsewhere

int[] listOfIds = (from employee in originalList
                   select employee.EntityId).ToArray();

// .. and then carry on with the EF query

Please note that queries on collections manifest as IEnumerable<T> instances, not as IQueryable<T> instances and have nothing to do with EF or LINQ to SQL or anyother DB or external data service.

IF THE TABLE IS NOT HUGE

Then you could refrain from actually using EF with complex queries, use it just for a "Full table fetch", temporarily store the results in your .NET process and use regular LINQ however you like.

The key to this story is fetching the entire table from the beginning. In your question you wrote:

return (from p in _context.Employee
            join pList in myEmployees on p.EmployeeID equals pList.EntitySourceID
            select new Person 
            {
                PersonID = pList.PersonID,
                FirstName = p.FirstName
                ... etc

Simply augment that with:

var entityList = _context.Employee.ToArray();

return (from p in entityList  // PLEASE NOTE THIS CHANGE ALSO
        join pList in myEmployees on p.EmployeeID equals pList.EntitySourceID
        select ...

TO WRAP IT UP

You can either:

• instruct the database to do the work but in which case you can't send it fancy .NET instances in the process
• do the work yourself, upstairs, in .NET

Either one side or the other (the database or the .NET process) needs to have all the cards (needs to have a clone of the other side) in order be able to perform the JOIN.

So it's just a game of compromise.

HOW ABOUT THE REMAINING CASE

If both THE TABLE and THE LIST are huge, then you're s****d. No- I'm just kidding.

Nobody heard of someone asking someone else to do wonders when they can't actually be done.

If this is the case, then you have to simplify the problem into a large number of smaller problems. I would suggest transforming into a TABLE HUGE + LIST NOT SO HUGE problem multiplied by N.

So how do you go about that?

List<MyEmployee> original = ...
// you take your list
// and you split it in sections of .. say 50 (which in my book is not huge for a database
// although be careful - the pressure on the database will be almost that of 50 selects running in parallel for each select)

// how do you split it?
// you could try this

public static IEnumerable<List<MyEmployee>> Split(List<MyEmployee> source, int sectionLength) {
    List<MyEmployee> buffer = new List<MyEmployee>();
    foreach (var employee in source) {
        buffer.Add(employee);
        if (buffer.Count == sectionLength) {
            yield return buffer.ToList(); // MAKE SURE YOU .ToList() the buffer in order to clone it
            buffer.Clear(); // or otherwise all resulting sections will actually point to the same instance which gets cleared and refilled over and over again
        }             
    }
    if (buffer.Count > 0)   // and if you have a remainder you need that too
       yield return buffer; // except for the last time when you don't really need to clone it
}

List<List<MyEmployee>> sections = Split(original, 50).ToList();

// and now you can use the sections
// as if you're in CASE 2 (the list is not huge but the table is)
// inside a foreach loop

List<Person> results = new List<Person>(); // prepare to accumulate results

foreach (var section in sections) {

    int[] ids = (from x in section select x.EntityID).ToArray();

    var query = from employee in _context.Employee
                where ids.Contains(employee.EmployeeId) 
                ... etc;

    var currentBatch = query.ToArray();

    results.AddRange(currentBatch);

}

Now you could say that this is simply a way of fooling the database into believing it has little work to do when in fact we're still flooding it with a lot of work and maybe making life hard for the other concurrent clients.

Well- yeah, but at least you could throttle down. You could Thread.Sleep between sections... You could use iterators (look them up) and actually not flood the RAM with records that will take a long time to process anyway but rather "stream things".

You have more control over the situation.

Good luck!

这篇关于加入表，收集EntityFramework的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！