Click here to Skip to main content
Sign Up to vote bad
good
See more: C#SQLLINQ
I have some columns(as mentioned below) in my table Customers
 
         CNo         City      Type         ResNo      Amount
         -------   ------   --------   --------   ---------  
            1            NY         CK            NULL         $220
            2            NY         CK            123            $220
            3            NY         CJ            NULL         $330
            4            NY         CJ            456            $330
            5            NY         CJ            789            $330
 

What is expected is ---
 
         City/Type   TotalCust      Total      TotalRes   LeftRes   ResAmount      Res%
         ---------   ---------      -----      ---------   -------   ---------      ------
            NY-CK            2            440           1               1            220            50%
            NY-CJ            3            990            2               1            660            66.66%
                                             -----                                       ------
                                             1430                                          880
 
M new to linq sql, any help would be aapreciated.Thanks
Posted 20 Feb '12 - 3:59


3 solutions

First of all thanks to Mark and Bob,
 
After their comments I went through the problem again and found it so simple,
so those who might be in trouble for them I am posting the answer what it worked for me.
 
I am having my data in the datatable "dt".
Firstly I have grouped the 'Type' and collected result in some variable.
 
var str = (from d in dt.AsEnumerable()
               group d by d["Type"] into m
               select new { Type = m.Key, Can = m });
 
Now here what actually happens is, the 'Type' in the datatable I am getting in Type property and the remaining data in Can property.
              
Once I group my data according to 'Type', then its simple to get the count.
 
//get the customers count with Type "CK"
int CntCK = str.ToArray()[0].Can.Where(c => c.ItemArray[0].ToString() != "").Select(c => c.ItemArray[0].ToString()).Count();
 
//get the reservation count with Type "CK"
int ResCntCK = str.ToArray()[0].Can.Where(c => c.ItemArray[3].ToString() != "").Select(c => c.ItemArray[3].ToString()).Count();
 
//get the amount of "CK" customer
var arrAmountCK = grpOfOfferType.ToArray()[0].Can.ToArray().Select(c => c.ItemArray[4].ToString());
double AmountCK = Convert.ToDouble(arrAmountCS.ToArray()[0]);
 

This is how I broke the it into parts and got the result.
Thanks All,
Stone
  Permalink  
Start by grouping by City and Type (you should be able to do that and the two first Total columns in a single query), and then do some post-processing on the objects that the grouping returns. As Mark says I doubt it is possible to do this in a single query, and certainly not in a short and elegant one.
  Permalink  
You are not going to get these results with a single LINQ, or SQL, statement. At least one that would be efficient or maintainable. Try breaking it down to the constituent pieces first.
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 OriginalGriff 375
1 Sergey Alexandrovich Kryukov 173
2 Abhinav S 168
3 Guirec Le Bars 120
4 Ron Beyer 100
0 Sergey Alexandrovich Kryukov 8,439
1 OriginalGriff 6,681
2 CPallini 3,553
3 Rohan Leuva 2,793
4 Maciej Los 2,234


Advertise | Privacy | Mobile
Web01 | 2.6.130516.1 | Last Updated 24 Feb 2012
Copyright © CodeProject, 1999-2013
All Rights Reserved. Terms of Use
Layout: fixed | fluid