实体框架中的嵌套查询

本文介绍了实体框架中的嵌套查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我收到以下异常：

不支持嵌套查询。 Operation1 ='Case'Operation2 ='收集'

与此查询

var Games = context.Games.Select（a => new GameModel { Members =（a.Type == 1？（a.UsersInGames.Where （b => b.GameID == a.ID&& b.StatusID == 1）.Select（c => new Member { ID = c.UserID， email = c.UserInfo.EmailAddress， screenName = c.UserInfo.ScreenName }））（a.Teams.Where（b => b.GameID == a .ID）.SelectMany（b => b.UsersInTeams.Where（c => c.StatusID == 1））。选择（d => new Member { ID = d。 UserID， email = d.UserInfo.EmailAddress， screenName = d.UserInfo.ScreenName ）））}） / pre>

当我在选择会员时不包含条件，查询工作正常。有没有办法可以在查询中执行条件？

解决方案

你高估了LINQ翻译为SQL的强大功能。不是一切都是可翻译的，因为LINQ的工作原理，没有编译器警告。

嵌套集合通常是a）不支持或b）最终可怕SELECT N + 1个查询。 EF要做的是返回一个对象树。 SQL不支持树状结果，所以您遇到对象关系阻抗不匹配，并且它会伤害。

我建议您获取嵌套收集数据作为第二个，完全独立的查询。这样可以让您更有控制力并保证工作正常。

作为一个不重要的旁边节点，您可能无法说服EF使用？过序。这很难翻译。想想你如何将这个写成SQL - 非常困难和复杂。

I am getting the following exception:

The nested query is not supported. Operation1='Case' Operation2='Collect'

with this query

var Games = context.Games.Select(a => new GameModel { Members = (a.Type == 1 ? (a.UsersInGames.Where(b => b.GameID == a.ID && b.StatusID == 1).Select(c => new Member { ID = c.UserID, email = c.UserInfo.EmailAddress, screenName = c.UserInfo.ScreenName })) : (a.Teams.Where(b => b.GameID == a.ID).SelectMany(b => b.UsersInTeams.Where(c => c.StatusID == 1)).Select(d => new Member { ID = d.UserID, email = d.UserInfo.EmailAddress, screenName = d.UserInfo.ScreenName ))) })

when I don't include the condition in selecting Members, the query works fine. Is there a way I can do the conditional inside the query?

解决方案

You're overestimating the power of LINQ translation to SQL. Not everything is translatable and there is no compiler warning for that due to the way LINQ works.

Nested collections are usually either a) not supported or b) end up in horrible SELECT N+1 queries. What you ask EF to do is to return an object tree. SQL does not support tree like results so you run into the object-relational impedance mismatch and it hurts.

I advise you to fetch the nested collection data as a second, completely separate query. That allows you more control and is guaranteed to work.

As a non-essential side-node, you will probably not be able to convince EF to use the ?: operator over sequences. That is very hard to translate. Think how you would write this as SQL - very hard and convoluted.