[asp.net]搜索查询 IQueryable<> MVC

标签: Asp.net Linq C#
发布时间: 2017/3/26 3:15:23
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我有一个问题与我的搜索查询,它不会从数据库中选择任何值时我在 MVC 中使用 IQueryable <> iam。

我的代码看起来像这样︰

IQueryable<Invoice> res =
            (from c in table
                join i in tableX on c.attr equals i.Id
                where c.attr== merchant
                select i);


        if (buyer > 0)
        {
            res = res.Where(i => i.Buyer.Msisdn == buyer);
        }

        if (send)
        {
            res =res.Where(i => i.Status == InvoiceStatus.Sent);                 
        }
        if (paid)
        {
            res=  res.Where(i => i.Status == InvoiceStatus.Paid);
        }
        if (notBilled)
        {
            res = res.Where(i => i.Status == InvoiceStatus.Open);
        }

        if (startDate <= endDate)
        {
            res =  res.Where(i => i.DateCreated >= startDate && i.DateCreated <= endDate);
        }

        return res.ToList();

如果不设置了 res,= res。Where (),而只是有 res.where() 查询从数据库中选择的值。有人可以请让我明白这就是为什么。你想要将查询结果存储在一个变量中。

解决方法 1:

你已经发布的代码看起来是实现 IQueryable 的适当方法。

res = res.Where(...)

基本上钉上附加条款信息直到查询执行在所在 res.ToList();

调用 res.Where 并不实际更改 res 查询。

你可能只限制你在什么地方条款太多和消除查询中的所有记录。

分析查询,以确定什么被查询,你试过吗?

我可以告诉你,如果在一个以上的 sendpaid ,或 notbilled 为 true,表示不会立即容许任何结果要从查询返回,当他们正在检查对 Status 列-可能不可能有多个值。

编辑

我不知道是否这有所帮助,但在这里是会通过一些错综复杂的 IQueryable 小提琴︰ https://dotnetfiddle.net/d70XKA

这里是来自小提琴的代码︰

public class Program
{

    public static void Main()
    {
        Thingy t = new Thingy();

        // Note execution is deferred until enumeration (in this case Count())
        var allData = t.GetData();
        Console.WriteLine("All Data count: {0}", allData.Count());

        // Select only valid records from data set (should be 2)
        var isValid = t.GetData();
        isValid = isValid.Where(w => w.IsValid);
        Console.WriteLine("IsValid count: {0}", isValid.Count());

        // select only records with an ID greater than 1 (should be 2)
        var gt1 = t.GetData();
        gt1 = gt1.Where(w => w.Id > 1);
        Console.WriteLine("gt 1 count: {0}", gt1.Count());

        // Here we're combining in a single statement, IsValid and gt 1 (should be 1)
        var isValidAndIdGt1 = t.GetData();
        isValidAndIdGt1 = isValidAndIdGt1.Where(w => w.IsValid && w.Id > 1);
        Console.WriteLine("IsValid and gt 1 count: {0}", isValidAndIdGt1.Count());

        // This is the same query as the one directly above, just broken up (could perhaps be some if logic in there to determine if to add the second Where
        // Note this is how you're doing it in your question (and it's perfectly valid (should be 1)
        var isValidAndIdGt1Appended = t.GetData();
        isValidAndIdGt1Appended = isValidAndIdGt1Appended.Where(w => w.IsValid);
        isValidAndIdGt1Appended = isValidAndIdGt1Appended.Where(w => w.Id > 1);
        Console.WriteLine("IsValid and gt 1 count w/ appended where: {0}", isValidAndIdGt1Appended.Count());

        // This is the same query as the one directly above, but note we are executing the query twice
        var isValidAndIdGt1AppendedTwice = t.GetData();
        isValidAndIdGt1AppendedTwice = isValidAndIdGt1AppendedTwice.Where(w => w.IsValid);
        Console.WriteLine("IsValid and gt 1 count w/ appended where executing twice: {0}", isValidAndIdGt1AppendedTwice.Count()); // 2 results are valid
        isValidAndIdGt1AppendedTwice = isValidAndIdGt1AppendedTwice.Where(w => w.Id > 1);
        Console.WriteLine("IsValid and gt 1 count w/ appended where executing twice: {0}", isValidAndIdGt1AppendedTwice.Count()); // 1 result is both valid and id gt 1

        // This is one of the things you were asking about - note that without assigning the additional Where criteria to the Iqueryable, you do not get the results of the where clause, but the original query - in this case there are no appended where conditions on the t.GetData() call, so you get the full result set.
        var notReallyValid = t.GetData();
        notReallyValid.Where(w => w.Name == "this name definitly does not exist");
        Console.WriteLine("where clause not correctly appended count: {0}", notReallyValid.Count());

        // vs
        var validUse = t.GetData();
        validUse = validUse.Where(w => w.Name == "this name definitly does not exist");
        Console.WriteLine("valid use count: {0}", validUse.Count());

    }

}

public class Thingy
{
    private List<Foo> _testData = new List<Foo>()
    {
        new Foo()
        {
            Id = 1,
            Name = "Alpha",
            Created = new DateTime(2015, 1, 1),
            IsValid = true
        },
        new Foo()
        {
            Id = 2,
            Name = "Beta",
            Created = new DateTime(2015, 2, 1),
            IsValid = false
        },
        new Foo()
        {
            Id = 3,
            Name = "Gamma",
            Created = new DateTime(2015, 3, 1),
            IsValid = true
        },          
    };

    public IQueryable<Foo> GetData()
    {
        return _testData.AsQueryable();
    }

    public void PrintData(IEnumerable<Foo> data)
    {
        // Note calling this will enumerate the data for IQueryable
        foreach (Foo f in data)
        {
            Console.WriteLine(string.Format("id: {0}, name: {1}, created: {2}, isValid: {3}", f.Id, f.Name, f.Created, f.IsValid));
        }
    }
}

public class Foo
{
    public int Id { get; set; }
    public string Name { get; set; }
    public DateTime Created { get; set; }
    public bool IsValid { get; set; }
}

说了这多,还有一些在你哪里子句筛选出您预期的数据。 正如你可以看到从上面的例子中 res = res.Where(...) 是非常不同于 res.Where(...) -前者是正确的做法。 后者,只忽略所有在哪里条款从你的发言完全,然后当 ToList() 被称为你得到的完整结果集作为无 Where 已添加条件 (保存 where c.attr== merchant 从原始的 var 创建)

官方微信
官方QQ群
31647020