?
Solved

Count with LINQ

Posted on 2008-10-05
5
Medium Priority
?
3,999 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 1200 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 800 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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

777 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