将数据源更改为SQL时发生异常

Question

我正在尝试生成一个查询，在其中我可以找到包含特定订单的所有条目。因此，我已经使用了linq2entities，但当试图针对linq2sql运行它时，我必须将其更改为左外部联接，然后应用distinct。我不想做的事。

Linq2实体：

List<Order> Orders = new List<Order>
{
new Order{ Id=1, Name="Order1"},
new Order {Id=2, Name="Order2"}
};
List<Product> Products = new List<Product>
{
new Product { Id = 1, Name = "Sugar", OrderId = 1 },
new Product {Id=2, Name="Flour", OrderId=1},
new Product { Id = 3, Name = "Milk", OrderId = 2 },
};
var query = (from order in Orders
join product in Products
on order.Id equals product.OrderId
into orderproducts
select new
{
User = order,
Roles = orderproducts
}).ToList();
var xx = query.Where(x => x.Products.Any(x => x.Name == "sugar"));// will produce a list of orders containing sugar.

我现在已经将这两个列表更改为数据库实体，并构建了相关的表，但我遇到了SQL错误。。。。

InvalidOperationException: Processing of the LINQ expression 'DbSet<Order>
.GroupJoin(
outer: DbSet<Prpduct<string>>,
inner: product => order.Id,
outerKeySelector: product=> product.orderId,
innerKeySelector: (p, o) => new {
...
})' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.

Answer 1

在我看来，您有包含Orders和Products的表。订单和产品之间存在一对多的关系：每个订单都有零个或多个产品，每个产品恰好属于一个订单，即外键Product.OrderId所指的订单。

每当您想查询包含零个子项或多个子项的项目时，如学校及其学生、客户及其订单，或订单及其产品，请考虑使用Enumerable.GroupJoin的重载之一，或者如果您的数据是从另一个流程获取的，则考虑使用相应的IQueryable版本。

虽然您的代码示例没有这么说，但错误消息给我的印象是您实际上正在使用GroupJoin。

用他们的产品获取订单：

IEnumerable<Order> orders = ...
IEnumerable<Product> products = ...
var queryOrdersWithTheirProducts = orders.GroupJoin(products,
order => order.Id,            // from every Order take the primary key
product => product.OrderId,   // from every Product take the foreign key
// parameter resultSelector:
// From every Order, with all its zero or more Products make one new:
(order, productsInThisOrder) => new
{
Order = order,
Products = productsInThisOrder.ToList(),
});

通常的要求是：给我属性。。。个订单，具有属性。。。他们的产品。在这种情况下，您应该使用以下resultSelector:

(order, productsInThisOrder) => new
{
// Select the requested Order properties:
Id = order.Id,
Date = order.Date,
...
// Select the Ordered Products:
Products = productsInThisOrder.Select(product => new
{
// again: select only the requested properties
Id = product.Id,
Name = product.Name,
Price = product.Price,
...
})
.ToList(),
});

现在您确实有了一个表示Orders with their Products的对象。

为了获得具有至少一个名称为"的产品的订单；糖"；其中：

string productName = "Sugar";
// keep only the Orders that have at least one Product with Name "Sugar"
var ordersWithSugar = queryOrdersWithTheirProducts
.Where(order => order.Products.Where(product => product.Name == productName)
.Any());

换句话说：从订单及其产品的序列中，只保留那些至少有一个名称等于productName的产品的订单。