在LINQ到SQL的映射计算性能，实际工作的SQL语句 [英] Mapping computed properties in Linq-to-SQL to actuall SQL statements

问题描述

我建立了我的LINQ到SQL类一些额外的功能，使事情变得更容易，因为我发展我的应用程序。例如，我已经定义了检索从合同列表活跃的合约属性。但是，如果我尝试使用这个属性在一个lambda EX pression或者一般的查询，要么抛出一个异常，没有SQL语句匹配的财产或它生成每个项目一个查询（=很多往返于服务器）。

该查询本身并不复杂f.ex：

  VAR activeContracts = customer.Contracts.Where（W =＆GT; w.ContractEndDate == NULL）;
 

而我想它改为：

  VAR activeContracts = customer.ActiveContracts;
 

主要的原因，我这样做是因为这将减少对我而言，逻辑错误，如果我在未来要改变什么定义了一个活跃的合约，我没有重做很多code

有没有一种方法来指定哪些SQL应该genereate属性。或者是有办法，以确保它是在查询中使用的象下面这样？

  VAR singleContractCustomers = db.Customers.Where（W =＆GT; w.ActiveContracts.Count（）== 1）;
 

解决方案

在单独访问，我怀疑有返回的IQueryable将工作查询 - 但是，我希望，当这是一个更大的前pression部分，前pression除preTER会抱怨（这似乎是你所描述的）。

不过，我怀疑你也许能打破它下来一点。试着增加（客户）：

 公共静态防爆pression＆LT; Func键＆LT;客户，布尔＆GT;＆GT; HasActiveContract
    {
        {返回卡斯特=＆GT; cust.Contracts.Count（）== 1; }
    }
 

那么你应该能够使用：

  VAR过滤= db.Customers.Where（Customer.HasActiveContract）;
 

显然，这是很难运行它（从这里开始），看看有什么TSQL谈到了，但是我会感到惊讶，如果这样做往返;我期望做COUNT（）在TSQL。作为最顶层的查询，你也应该能包住这样的：

 公开的IQueryable＆LT;客户＆GT; CustomersWithActiveContract
    {
        {返回Customers.Where（Customer.HasActiveContract）; }
    }
 

是否有任何的工作？

I have created some extra functionality on my Linq-to-SQL classes to make things easier as I develop my applications. For example I have defined a property that retrieves active contracts from a list of contracts. However if I try to use this property in a lambda expression or in general in a query it either throws an exception that there is no SQL statement matching that property or it generates one query per item (= a lot of roundtrips to the server).

The queries themselves are not overly complex f.ex:

var activeContracts = customer.Contracts.Where(w => w.ContractEndDate == null);

Whereas I would like it to read as:

var activeContracts = customer.ActiveContracts;

The main reason for me doing this is because it will minimize logical errors on my part and if I in the future want to change what defines an active contract I don't have to redo a lot of code.

Is there a way to specify on a property what SQL it should genereate. Or is there a way to make sure it is usable in a query like below?

var singleContractCustomers = db.Customers.Where(w => w.ActiveContracts.Count() == 1);

解决方案

When accessed individually, I suspect that having a query that returns IQueryable would work - however, I expect that when this is part of a larger Expression, the expression interpreter will complain (which seems like what you are describing).

However, I suspect that you might be able to break it down a bit. Try adding (to customer):

    public static Expression<Func<Customer, bool>> HasActiveContract
    {
        get { return cust => cust.Contracts.Count() == 1; }
    }

Then you should be able to use:

    var filtered = db.Customers.Where(Customer.HasActiveContract);

Obviously it is hard to run it (from here) to see what TSQL it comes up with, but I'd be surprised if that does roundtrips; I would expect that to do the COUNT() in the TSQL. As a topmost query, you should also be able to wrap this:

    public IQueryable<Customer> CustomersWithActiveContract
    {
        get { return Customers.Where(Customer.HasActiveContract); }
    }

Does any of that work?

这篇关于在LINQ到SQL的映射计算性能，实际工作的SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！