问题描述:

I want to query my item in table Items, where the last update of each item must be less than 91 days old (from last update till now) and the quantity > 0.

This is my code in the Model:

public IList<Item> GetAllProducts()

{

var ien_item = from i in this.DataContext.Items

orderby i.LastUpdated descending

select i;

return ien_item.ToList().Where(

s =>

HelperClasses.HelperClass.IsLastUpdate(s.LastUpdated.Value) == true

&&

(s => s.Quantity) > 0

)

.ToList();

}

Anyone can solve it? Thanks.

网友答案:

We don't really know what's not working here. EDIT: Merlyn spotted it; your lambda syntax is messed up. There's more to do here though.

However, I'd have thought you'd want this:

public IList<Item> GetAllProducts()
{
     var lastUpdateLimit = DateTime.UtcNow.Date.AddDays(-91);
     var query = from item in DataContext.Items
                 where item.Quantity > 0 && item.LastUpdated >= lastUpdateLimit
                 orderby item.LastUpdated descending
                 select item;
     return query.ToList();
}

Note that this is able to do all the querying at the database side instead of fetching all the items and filtering at the client side. It does assume that HelperClasses.HelperClass.IsLastUpdate is simple though, and basically equivalent to the filter I've got above.

(One additional point to note is that by evaluating UtcNow.Date once, the result will be consistent for all items - whereas if your code evaluates "today" on every call to IsLastUpdate, some values in the query may end up being filtered against a different date to other values, due to time progressing while the query is evaluating.)

EDIT: If you really need to use HelperClasses.HelperClass.IsLastUpdate then I'd suggest:

public IList<Item> GetAllProducts()
{
     var query = from item in DataContext.Items
                 where item.Quantity > 0
                 orderby item.LastUpdated descending
                 select item;
     return query.AsEnumerable()
                 .Where(s => HelperClass.IsLastUpdate(s.LastUpdated.Value))
                 .ToList();
}

... then at least the quantity filter is performed at the database side, and you're not creating a complete buffered list before you need to (note the single call to ToList).

网友答案:

The problem is your lambda syntax. You're trying to define a second lambda while in the middle of defining a first lambda. While this is possible to do, and useful in some contexts, it is sort of an advanced scenario, and probably won't be useful to you until you know you need it.

Right now, you don't need it. Unless you know you need it, you don't need it :)

So -

Instead of what you've written:

.Where(
    s =>
        HelperClasses.HelperClass.IsLastUpdate(s.LastUpdated.Value) == true 
            && (s => s.Quantity) > 0
    )

Write this instead:

.Where(
    s =>
        HelperClasses.HelperClass.IsLastUpdate(s.LastUpdated.Value) == true 
            && s.Quantity > 0 // Notice I got rid of the extra lambda here
    )

If you're morbidly curious:

The compile error you got is because you didn't define your second lambda correctly. It redefined a variable you'd already used (s), and you were trying to check if a lambda was greater than zero. That makes no sense. You can only compare the result of a lambda to some value. It's like calling a function. You don't compare functions to numbers - you compare the result you get when calling a function to a number.

网友答案:

Easy ...

public IList<Item> GetAllProducts()
{
     var ien_item = 
          from i in DataContext.Items
          where 
               HelperClasses.HelperClass.IsLastUpdate(i.LastUpdated.Value)
               && s.Quantity > 0
          orderby i.LastUpdated descending
          select i;

     return ien_item.ToList();
}

Linq to SQL: Methods are not allowed (linq is not magic and can not convert C# methods to TSQL)
http://msdn.microsoft.com/en-us/library/bb425822.aspx

Linq to Object: while looking the same, it is much more powerful than linq to SQL... but can not query SQL databases :)
http://msdn.microsoft.com/en-us/library/bb397919.aspx

Linq to XML: same as linq to Object, with xml object
http://msdn.microsoft.com/en-us/library/bb387098.aspx

Linq to Dataset: not the same as Linq to SQL !
http://msdn.microsoft.com/en-us/library/bb386977.aspx

Other linq providers:
http://en.wikipedia.org/wiki/Language_Integrated_Query

相关阅读:
Top