Sql and LINQ query conversion

bakhtawa...

SOLVED CLOSED ACTIVE ANSWERED
User: bakhtawar
Posted: on Jun 27, 2016 06:20 AM
Forum: SQL Server
Answer: 1
Views: 2227
Sample Code: Download

I have following table columns in tblvv ID MID VName Speed Name columns in tblrv ID FFID OName regno RID columns in tblre RegionID Region StartDate EndDate here ID in tblrvv and MID in tblvv is same i want Vname according to region sample data is in tblvv

ID    MID	       VName
1         1	       DPSB
2         1	       MV
3         1            MV
4         2            dp3sb
5         2            RV
6         3            RV
7         3            ASB
8         3            ASB
9         3            dpsb
10        3             MV

tblrv

ID	FFID	OwnerName	RegNo       RID
1	10366	  Max	          AJ-29      1
2	10444	  John	          AV-99       1
3	10454	  Pete	          AL-95       2
4       1053      david            PO-99      3
5        10788    Eagl              UI-98     3
6        10876    Rainb             QY-17     4

tblre

RID	           Region	      StartDate         EndDate
1	            UK	              2014-02-01 	2014-02-28 
2	            US	             2014-02-01 	2014-02-28 
3	            Paris	     2014-02-01 	2014-02-28
4	            london	     2015-02-03          2014-02-10 
5                   france           2015-03-01         2014-03-07 
6                    denmark         2016-03-14 	2014-03-21 
7                    india           2016-04-01 	2014-04-07

now when i run this query

select * from tblre WHERE RID=1
SELECT * FROM tblrv  where ID=1
SELECT * FROM tblvv  where MID=1

table re

RegionID	Region	        StartDate	EndDate
1	      UK	       2014-02-01 	2014-02-28

table rv then this return like this

ID	FFID	OwnerName	  RegNo
1	10366	Max	           AJ-29

and table vv

ID	MasterID	VName
2	1	            MV
3	1	            DPSB
4       1                   MV

NOW I Want data like this

vnametotal           RegionID   Vname
      1                1        DPSB
      2                1        MV

MEANS there is 1 DPSB in RID 1 and 2 MV in RID 1 as check in tblrvv i try this query select count(vname) as total,tblre.RID,Vname from tblvv join tblrv on tblrv.ID=tblvv.MID join tblre on tblre.RID = tblrv.RID group by VName, tblre.RID BUT this query not show me correct result

Download FREE API for Word, Excel and PDF in ASP.Net

Shashika...

0 1

Reply
ANSWER
User: Shashikant
Replied: on Jun 27, 2016 08:32 AM
Report

Please refer below code.

List<tblre> re = new List<tblre>
    {
        new tblre{
                RID =1,
                Region = "UK",
                StartDate = DateTime.Parse("2014-02-01"),
                EndDate= DateTime.Parse("2014-02-28 ")
            },
    };

List<tblrv> rv = new List<tblrv>
    {
        new tblrv{
            ID=1,
            FFID = 10366,
            OwnerName ="Max",
            RegNo = "AJ-29",
            RID=1
        }
    };

List<tblvv> vv = new List<tblvv>
    {
            new tblvv
            {
                ID = 2,
                MID=1,
                VName="MV"
            },
            new tblvv
            {
                ID = 3,
                MID=1,
                VName="DPSB"
            },
            new tblvv
            {
                ID = 4,
                MID=1,
                VName="MV"
            },
    };

var result = from tbvv in vv
                join tbrv in rv on tbvv.MID equals tbrv.RID
                join tbre in re on tbrv.RID equals tbre.RID
                group new { A = tbre, B = tbvv } by tbvv.VName into g
                select new { VNametotal = g.Key.Count(), VName = g.Select(z => z.B.VName), RegionID = g.Select(x => g.Select(y => y.A.RID)) };
public class tblvv
{
    public int ID { get; set; }
    public int MID { get; set; }
    public string VName { get; set; }
}

public class tblrv
{
    public int ID { get; set; }
    public int FFID { get; set; }
    public string OwnerName { get; set; }
    public string RegNo { get; set; }
    public int RID { get; set; }
}

public class tblre
{
    public int RID { get; set; }
    public DateTime EndDate { get; set; }
    public string Region { get; set; }
    public DateTime StartDate { get; set; }
}

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Sql and LINQ query conversion

Company

Explore

Follow us

Contact Us