• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4003
  • Last Modified:

Count with LINQ

Hi all,
I'm trying to implement LINQ query on some tables

I have a table called "Raffles", "Orders", "OrderTickets":

Raffles:
-------------------
-ID
-Name
-.......

Orders:
-----------------
-ID
-RaffleID
-Enabled (bit)
-.........

OrderTickets:
-------------------
-ID
-OrderID
-TicketNumber (int)
-Enabled(bit)

I want to get the count of all tickets for the raffle - As long as Enabled = true (in orders and in orderTickets as well).

This is what I did:

        var topRaffles = from rf in db.Raffles
                         select new
                         {
                             rf.ID,
                             rf.Name,
                             TicketsBought = rf.Orders.Count(p1 => p1.Enabled)
                         };

The problem is that now it's the count for all the orders that have the same RaffleID and Enabled = true. But I want also that OrderTickets.Enabled = true.

I tried doing something like:
        var topRaffles = from rf in db.Raffles
                         select new
                         {
                             rf.ID,
                             rf.Name,
                             TicketsBought = rf.Orders.Count(p1 => p1.Enabled && p1 => p1.OrderTickets.Enabled)
                         };

I also tried:
        var topRaffles = from rf in db.Raffles
                         select new
                         {
                             rf.ID,
                             rf.Name,
                             TicketsBought = (from or in db.Orders where or.Enabled == true && or.RaffleID == rf.ID select or.OrderTickets.Count(p => p.Enabled))
                         };

but it doesn't work.
What am I doing wrong?
0
giligatech
Asked:
giligatech
  • 2
  • 2
2 Solutions
 
giligatechAuthor Commented:
This is what I did eventually.
tell me if you have a better solution.
        var topRaffles = from rf in db.Raffles
                         select new
                         {
                             rf.ID,
                             rf.Name,
                             TicketsBought = (from or in rf.Orders join ot in db.OrderTickets on or.ID equals ot.OrderID where or.Enabled == true && ot.Enabled == true select or.OrderTickets.Count()).Count()
                         };

Open in new window

0
 
novynovCommented:
I believe that the code snippet below should yield the correct results. Of course, as with SQL so it is with LINQ - normally many ways to do the same thing...though some may be more efficient than others...and some may produce more readable code than others...and some may just be a matter of style.

Notice the generated SQL (see below).

- Your query:

SELECT [t0].[Id], [t0].[Name], (
    SELECT COUNT(*)
    FROM [Orders] AS [t1]
    INNER JOIN [OrderTickets] AS [t2] ON [t1].[Id] = [t2].[OrderId]
    OUTER APPLY (
        SELECT COUNT(*) AS [value]
        FROM [OrderTickets] AS [t3]
        WHERE [t3].[OrderId] = [t1].[Id]
        ) AS [t4]
    WHERE ([t1].[Enabled] = 1) AND ([t2].[Enabled] = 1) AND ([t1].[RaffleId] = [t0].[Id])
    ) AS [TicketsBought]

My query:

SELECT COUNT(*) AS [TicketCount], [t2].[Id] AS [RaffleId], [t2].[Name] AS [RaffleName]
FROM [OrderTickets] AS [t0]
INNER JOIN [Orders] AS [t1] ON [t1].[Id] = [t0].[OrderId]
INNER JOIN [Raffles] AS [t2] ON [t2].[Id] = [t1].[RaffleId]
WHERE ([t0].[Enabled] = 1) AND ([t1].[Enabled] = 1)
GROUP BY [t2].[Id], [t2].[Name]

Interestingly, my first attempt (not shown) chose the raffle name out of the grouping via grouping.First().Order.Raffle.Name in the final select expression. However, the generated SQL was abominable. I won't even show the query. So, I went with grouping by multiple criteria (Raffle Id and Name) - which requires the definition of an anonymous type.

I hope this helps. BTW, My apologies for the difference in case in the "Id" field. It's the way I set my test db up.
      var q = from orderTicket in context.OrderTickets
                        where orderTicket.Enabled == true && orderTicket.Order.Enabled == true
                        group orderTicket by new { RaffleId = orderTicket.Order.Raffle.Id, RaffleName = orderTicket.Order.Raffle.Name } into grouping
                        select new
                        {
                            RaffleId = grouping.Key.RaffleId,
                            RaffleName = grouping.Key.RaffleName,
                            TicketCount = grouping.Count()

Open in new window

0
 
Fernando SotoCommented:
Hi giligatech;

In the original question it looked like you what to  know how many tickets each person got. The code snippet below does that.

Fernando

RafflesDataContext db = new RafflesDataContext();
 
var topRaffles = from OrderTicket in db.OrderTickets
                 where OrderTicket.Enabled == true && OrderTicket.Orders.Enabled == true
                 group OrderTicket by OrderTicket.Orders.Raffles.ID.ToString() + "|" + OrderTicket.Orders.Raffles.Name;
 
foreach (var grouping in topRaffles)
{
    int ticketCount = 0;
    String[] Key = grouping.Key.Split(new Char[] {'|'});
    Console.Write("Raffle ID = {0} - Name = {1} - ", Key[0], Key[1]);
    foreach( var tCount in grouping )
    {
        ticketCount += (int) tCount.Count;
    }
    Console.WriteLine(" TicketsBought = " + ticketCount);
}

Open in new window

0
 
Fernando SotoCommented:
Hi giligatech;

After looking at novynov solution I worked out a little better solution then the one I posted first returning the number of total tickets for each person.

Fernando

RafflesDataContext db = new RafflesDataContext();
 
var topRaffles = from OrderTicket in db.OrderTickets
                 where OrderTicket.Enabled == true && OrderTicket.Orders.Enabled == true
                 group OrderTicket by new { OrderTicket.Orders.Raffles.ID, OrderTicket.Orders.Raffles.Name } into ticketGrouping
                 select new
                 {
                     RaffleID = ticketGrouping.Key.ID,
                     Name = ticketGrouping.Key.Name,
                     TicketsBought = ticketGrouping.Sum(tg => tg.Count)
                 };
 
foreach (var top in topRaffles)
{
    Console.WriteLine("RaffelID = {0} - Name = {1} - TicketsBought = {2}", top.RaffleID, top.Name, top.TicketsBought);
}

Open in new window

0
 
giligatechAuthor Commented:
As novynov said, there are many ways to the the linq query.
Thank you for your support.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now