CODEWITHSUNDEEP
sql-serverlinq
user6628729
user6628729

Reputation: 323

Linq query count

select count(tblVV.VNme) as total, 
      tblvV.VNme
from tblVV
inner join tblRV
on tblVV.MID=tblRV.ID
inner join tblRe
on tblRV.RID=tblRe.RID
where tblRe.StartDate>= '2016-07-01 00:00:00' and
    tblRe.EndDate<= '2016-07-31 23:59:59' and
    tblRe.Reg= 'uk' and
    tblRV.RegNo='BR72' and
    tblVV.VNme <>''
group by tblVV.VNme

For the above query I get:

total Vame
1     DDSB
11    MV

The above SQL query shows me correct data so now i try to convert above query to linq query 

[WebMethod]
public static string GetVo(string RegNo)
{
    string data = "[";
    try
    {
         Ts1 DB = new Ts1();
         var re = (from vehvoila in DB.tblVV
                   join regveh in DB.tblRV on vehvoila.MID equals regveh.ID
                   join reg in DB.tblReg on regveh.RID equals reg.RID
                   where regveh.RegNo == RegNo &&
                         vehvoila.Vame != ""
                   group vehvoila by vehvoila.Vame into g
                   select new
                   {  
                       VNme = g.Key,
                       cnt = g.Select(t => t.Vame).Count()
                    }).ToList();

    if (re.Any())
    {
        data += re.ToList().Select(x => "['" + x.Vame + "'," + x.cnt + "]")
          .Aggregate((a, b) => a + "," + b);
    }
    data += "]";
}

linq query show me return data like this 

[['DDSB',1],['DPSB',1],['DSB',109],['MV',39],['PSB',1]]

Whereas I want data this 

[['DDSB',1],['MV',11]]

Now the data which return SQL query is correct so how I correct linq query

Note: forget fromdate,todate,region parameter in SQL query . because I have page in which I put dropdown and fromdate and todate picker and there is button so when I select values i.e. UK, and dates then data is display in table then when I click on any row in table then I want to get this data in data +=”]”;

actually above linq query work behind clicking on row

total Vame
1     DDSB
11    MV

Upvotes: 0

Views: 115

Answers (1)

Gilad Green
Gilad Green

Reputation: 37281

You can write it all like this:

Ts1 db = new Ts1();
var result = (from vehvoila in db.tblVV
              join regveh in db.tblRV on vehvoila.MID equals regveh.ID
              join reg in db.tblReg on regveh.RID equals reg.RID
              where reg.StartDate >= new DateTime(2016, 7, 1) &&
                    reg.EndDate < new DateTime(2016, 8, 1) &&
                    reg.Reg == "uk" &&
                    regveh == "BR72" &&
                    vehvoila != ""
              group vehvoila by vehvoila.Vame into g
              select $"[{g.Key},{g.Count()}]");

var data = $"[{string.Join(",", result)}]";

  1. Because you only use the result for the creation of the string in the select I just return the string formatted for a single item and then later used string.Join instead of using the .Aggregate - I think a bit cleaner

  2. The $"{}" syntax is the C# 6.0 string interpolation

  3. In the condition of the EndDate I decided to use < instead of the <= with the change of the date - At least in oracle when you partition the table by date it is better for performance - maybe also in sql server

Without string interpolation:

Ts1 db = new Ts1();
var result = (from vehvoila in db.tblVV
              join regveh in db.tblRV on vehvoila.MID equals regveh.ID
              join reg in db.tblReg on regveh.RID equals reg.RID
              where reg.StartDate >= new DateTime(2016, 7, 1) &&
                    reg.EndDate < new DateTime(2016, 8, 1) &&
                    reg.Reg == "uk" &&
                    regveh == "BR72" &&
                    vehvoila != ""
              group vehvoila by vehvoila.Vame into g
              select new { Key = g.Key, Count = g.Count()})
             .AsEnumerable()
             .Select(g => string.Format("[{0},{1}]",g.Key, g.Count));

var data = string.Format("[{0}]",string.Join(",", result));

Upvotes: 1

Related Questions