Solved

Count with LINQ

Posted on 2008-10-05
5
3,992 Views
Last Modified: 2013-11-11
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
Comment
Question by:giligatech
  • 2
  • 2
5 Comments
 

Author Comment

by:giligatech
ID: 22644475
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
 
LVL 4

Accepted Solution

by:
novynov earned 300 total points
ID: 22645572
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
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 22645707
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
 
LVL 63

Assisted Solution

by:Fernando Soto
Fernando Soto earned 200 total points
ID: 22645867
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
 

Author Closing Comment

by:giligatech
ID: 31503169
As novynov said, there are many ways to the the linq query.
Thank you for your support.
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question