创建LINQ to SQL的可重用块 [英] Creating reusable chunks of LINQ to SQL

问题描述

我正试图将linq分解为sql查询，以使其更具可读性.

I am trying to break up linq to sql queries to make them a bit more readable.

说我想退回上一年有100多个订单的产品的所有订单.我有这个查询:

Say I want to return all orders for product which in the previous year had more than 100 orders. I have this query:

from o in _context.Orders
where (from o1 in _context.Orders 
       where o1.Year == o.Year - 1 && o1.Product == o.Product
       select o1).Count() > 100
select o;

我想做的就是将嵌套查询放在可重用的函数中:

What I'd like to be able to do is to put the nested query in a reusable function:

private IQueryable<Order> LastSeasonOrders(Order order)
{
    return (from o in _context.Orders 
            where o.Year == order.Year - 1 && o.Product == order.Product
            select o);
}

然后我可以将原始查询更改为:

which then lets me change the original query to:

from o in _context.Orders
where LastSeasonOrders(o).Count() > 100
select o;

但是这无法正常工作，但有例外，说明在运行查询时无法将方法调用转换为SQL.

This doesn't work however with an exception saying that the method call cannot be translated to SQL when the query is run.

有关实现此目标的正确方法的任何快速提示吗?

Any quick tips on the correct way to achieve this?

推荐答案

--p

void Main()
{
    TypedDataContext _context = ...

    var query = 
        (
            from o in _context.Orders  
            where LastSeasonOrders(_context , o).Count() > 100 
            select o    
        );
     ...
 }      


public static Func<TypedDataContext, Order, IQueryable<Order>> 
     LastSeasonOrders = CompiledQuery.Compile
     (( TypedDataContext _context, Order order) =>

        from o in _context.Orders
        where o.Year == order.Year - 1 && o.Product == order.Product
        select o            
);          

?

最好验证产生的sql与原始查询产生的sql相同.

It would be best to verify that the sql produced is the same as that produced by your original query.

这篇关于创建LINQ to SQL的可重用块的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！