如何编写动态Linq来计算匹配的数字 [英] How to write dynamic Linq to count matching numbers

问题描述

我在数据库中有两个表。票和门票数我想写Linq来计算与传递给这个函数的数字匹配的票数。因为我们不知道有多少个数字必须匹配，所以Linq必须是动态的...我的理解。

  public int CountPartialMatchingTicket（IList< int> numbers）
 {
 //代码在哪里？ = _ =; 
} 
  

例如，现在有数据库中有3个门票，我想计数所有那些具有数字3和4的人。

 （1）1 2 3 4 
（2）1 3 4 6 7 
（3）1 2 3 
  

在这种情况下，函数应该返回2，因为票（1）和（2）具有匹配的数字。
在另一种情况下，如果被要求匹配1，2，3，那么我们应该再次返回2。

这是数据库中的这两个表的样子：

 票：
 TicketId，Name，AddDateTime 
 
 TicketNumbers：
 Id，TicketId，Number 
  

我从来没有使用过Dynamic Linq，所以只是为了防止这样我在我的cs文件的顶部。

  using System; 
使用System.Collections.Generic; 
使用System.Linq; 
使用System.Web; 
使用LottoGen.Models; 
使用System.Linq.Dynamic; 
  

首先，我不知道我应该如何编写Linq行来修复数量。我想SQL将如下所示：

  SELECT TicketId，COUNT（0）AS Expr1 
 FROM TicketNumber 
 WHERE（Number = 3）OR（Number = 4）
 GROUP BY TicketId 
  

不过这不是我想要的。上面的查询会让我有一个3或4的票，但我只是想要有两个票的票。我想它必须以某种方式嵌套以返回单个计数。如果我不得不使用我的想象力来完成这个功能，那就是这样的：

  public int CountPartialMatchingTicket（IList< int> numbers）
 {
 string query =; 
 foreach（int number in numbers）{
 query + =Number =+ number.ToString（）+AND; 
} 
 //我知道..有一个尾随AND .. lazy 
 int count = DbContext.TicketNumbers.Where（query）.Count（）; 
返回计数; 
} 
  

哦等一下。没有动态Linq那里...以上看起来像我会在PHP中做的，查询语句显然不会做任何有用的事情。我在做什么？ ：（

在一天结束时，我想输出一个小桌子到这样的网页。

 门票配对门票
 ------------------------------- ---- 
 3 4 2 
  

三位一体，帮助！

解决方案

  public int CountPartialMatchingTicket（IList< int>数字）
 {
 var arr = number.ToArray（）; 
 int count = DbContext.Tickets 
 .Count（tk => arr.All（n => tk.TicketNumbers.Any（tn => tn.Number == n））; 
返回计数; 
} 
  

更新：如果你不介意，我如何将这个查询限制在特定的AddDateTime（整天）上的票据？

Count（）方法调用是WHERE条件，所以只需扩展：

  DateTime targetDate = ......; 
 DateTime tooLate = targetDate.AddDay（1）; 
 
 int count = DbContext.Tickets 
 .Count（tk => 
 targetDate< tk.AddDateTime&& tk.AddDateTime< tooLate 
& ;& arr.All（n => tk.TicketNumbers.Any（tn => tn.Number == n））; 
  

I have two tables in database. Ticket and TicketNumbers. I would like to write Linq to count the number of tickets that have numbers matching those passed into this function. Since we don't know how many numbers must be matched, the Linq has to be dynamic ... to my understanding.

public int CountPartialMatchingTicket(IList<int> numbers)
{
    // where's the code? =_=;
}

Say for example there are 3 Tickets in the database now and I want to count up all those that have the numbers 3 and 4.

(1) 1 2 3 4
(2) 1 3 4 6 7
(3) 1 2 3

In this case the function should return 2, since ticket (1) and (2) have the matching numbers. In another case if asked to match 1, 2, 3, then again we should be returned 2.

Here's what those two tables in the database look like:

Ticket:
TicketId, Name, AddDateTime

TicketNumbers:
Id, TicketId, Number

I've never used Dynamic Linq before, so just in case this is what I have at the top of my cs file.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using LottoGen.Models;
using System.Linq.Dynamic;

First things first though, I don't even know how I should write the Linq line for a fixed amount of numbers. I suppose the SQL would be like this:

SELECT        TicketId, COUNT(0) AS Expr1
FROM          TicketNumber
WHERE         (Number = 3) OR (Number = 4)
GROUP BY      TicketId

However this isn't what I want either. The above query would get me Tickets that have either a 3 or a 4 - but I just want the tickets that have BOTH numbers. And I guess it has to be nested somehow to return a single count. If I had to use my imagination for completing the function then, it would be something like this:

public int CountPartialMatchingTicket(IList<int> numbers)
{
    string query = "";
    foreach(int number in numbers) {
        query += "Number = " + number.ToString() + " AND ";
    }
    // I know.. there is a trailing AND.. lazy
    int count = DbContext.TicketNumbers.Where(query).Count();
    return count;
}

Oh wait a minute. There's no Dynamic Linq there... The above is looking like something I would do in PHP and that query statement obviously does not do anything useful. What am I doing? :(

At the end of the day, I want to output a little table to the webpage looking like this.

Ticket             Matching Tickets
-----------------------------------
3 4                               2

Trinity, help!

解决方案

 public int CountPartialMatchingTicket(IList<int> numbers)
 {
     var arr = numbers.ToArray();
     int count = DbContext.Tickets
             .Count(tk=>arr.All(n=> tk.TicketNumbers.Any(tn=>tn.Number== n));
     return count;
 }

UPDATE: "If you don't mind, how would I limit this query to just the tickets made on a particular AddDateTime (the whole day)?"

The part inside the Count() method call is the WHERE condition, so just extend that:

    DateTime targetDate = ......;
    DateTime tooLate = targetDate.AddDay(1);

     int count = DbContext.Tickets
             .Count(tk=>
                       targetDate < tk.AddDateTime &&  tk.AddDateTime < tooLate
                       &&  arr.All(n=> tk.TicketNumbers.Any(tn=>tn.Number== n));

这篇关于如何编写动态Linq来计算匹配的数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！