Solved

Count with LINQ

Posted on 2008-10-05
5
3,995 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

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…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

739 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