在Linq to SQL查询中执行计数（创建为IQueryable）获取所有行

Class Customer has the following method, which returns an IQueryable with the linq query to get the active sales for a customer:

Class Customer具有以下方法,该方法返回带有linq查询的IQueryable以获取客户的活动销售:

    public IQueryable<SalesHeader> QueryCurrentSales()
    {
        // this.SalesHeaders is an association defined in the DBML between
        // the Customer and SalesHeader tables (SalesHeader.CustomerId <-> Customer.Id).
        return this.SalesHeaders
            .Where(sh => sh.Status == 1).AsQueryable();
    }

The idea is to centralise the query definition in a single point, so that later we can get the SalesHeader rows, do a count, paginate using the PaginatedList class (from NerdsDinner), and add further Where conditions when searching Sales within active SalesHeaders.

我们的想法是将查询定义集中在一个点上,以便稍后我们可以使用PaginatedList类(来自NerdsDinner)获取SalesHeader行,进行计数,分页,并在活动SalesHeaders中搜索Sales时进一步添加Where条件。

However, after running the SQL Sever profiler, I've discovered that doing the following, gets all the rows and then does the Count in memory.

但是,在运行SQL Sever分析器之后,我发现执行以下操作,获取所有行,然后在内存中执行Count。

    // cust is an object of type Customer.
    int rows = cust.QueryCurrentSales().count();

I guess that the problem lies in the fact that we have used the AsQueryable() method, but I don't know what would be the correct way to accomplish what we intended.

我想问题在于我们使用了AsQueryable()方法,但我不知道实现我们预期的正确方法是什么。

2 个解决方案

#1

You haven't shown what SalesHeaders is. The fact that you feel you need to do AsQueryable suggests that it's returning an IEnumerable<T> rather than an IQueryable<T>... which is probably the problem. If you could show SalesHeaders, we may be able to help.

您尚未显示SalesHeaders是什么。你觉得你需要做AsQueryable这一事实表明它正在返回一个IEnumerable 而不是一个IQueryable ......这可能就是问题所在。如果您可以展示SalesHeaders,我们可能会提供帮助。

It's possible that instead of using AsQueryable you may be able to just cast to IQueryable<SalesHaeder> if the SalesHeaders property is returning an expression which could actually be returned as IQueryable<SalesHeaders> in the first place - but in that case I'd suggest changing the property type instead.

如果SalesHeaders属性首先返回一个实际上可以作为IQueryable 返回的表达式,那么您可能只能转换为IQueryable 而不是使用AsQueryable - 但在这种情况下,我建议改为改变属性类型。

EDIT: Okay, if it's an entity set then that's probably tricky to fix directly.

编辑:好的,如果它是一个实体集,那么直接修复可能很棘手。

I know it's ugly, but how about something like:

我知道这很难看,但是如下:

public IQueryable<SalesHeader> QueryCurrentSales()
{
    // You *may* be able to get away with this in the query; I'm not sure
    // what LINQ to SQL would do with it.
    int id = this.Id;

    return context.SalesHeaders
                  .Where(sh => sh.Status == 1 && sh.CustomerId == id);
}

Here I'm assuming you have some way of getting to the data context in question - I can't remember offhand whether there's an easy way of doing this in LINQ to SQL, but I'd expect so.

在这里,我假设您有一些方法可以获得有问题的数据上下文 - 我不记得是否有一种简单的方法在LINQ to SQL中执行此操作,但我希望如此。

#2

This works right. Doing count() on SQL Server.

这是正确的。在SQL Server上执行count()。

// cust is an object of type Customer.
int rows = cust.QueryCurrentSales().AsQueryable<SalesHeaders>().count();

#1