Solved

Count with LINQ

Posted on 2008-10-05
5
3,988 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 62

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 62

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
In WPF / C# binding a local database in code behind 1 24
Connection String 16 43
Error on link 14 37
What do you call this line of code in this .NET Core page? 4 20
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

912 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now