asked on

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?

giligatech

ASKER

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

ASKER CERTIFIED SOLUTION

novynov

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fernando Soto

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);
}