Linq query to join multiple tables from database in ASP.Net

Hi AliYilmaz,

In the join condition you need to check with primary table primary key column with foreign table foreign key column. Not with both primary key columns.

I have created sample. Check this example. Now please take its reference and correct your code.

SQL

CREATE TABLE T_Talepler(ID INT PRIMARY KEY,TalepNo VARCHAR(20),GidisTarihi DATETIME,DonusTarihi DATETIME,TalepTarihi DATETIME,KisiSayisi INT,
						Klas INT,Butce INT,KonaklamaID INT,TeklifID INT,ILID INT,ILCEID INT,SirtekID INT,KlasID INT,OK INT)
INSERT INTO T_Talepler VALUES(1,'T-01-10-17','2017-10-12 16:33:11.980','2017-10-12 16:33:11.980','2017-10-12 16:33:11.980',1,1,500,1,NULL,1,34,1,1,1)
INSERT INTO T_Talepler VALUES(2,'T-02-10-17','2017-12-12 22:11:58.420','2017-12-12 22:11:58.420','2017-12-12 22:11:58.420',2,2,500,2,NULL,1,35,1,2,1)

CREATE TABLE T_Klas(KlasID INT PRIMARY KEY,KlasName VARCHAR(50))
INSERT INTO T_Klas VALUES(1,'Klas 1')
INSERT INTO T_Klas VALUES(2,'Klas 2')

CREATE TABLE T_Konaklama(KonaklamaID INT PRIMARY KEY,KonaklamaName VARCHAR(50))
INSERT INTO T_Konaklama VALUES(1,'Konaklama 1')
INSERT INTO T_Konaklama VALUES(2,'Konaklama 2')

CREATE TABLE T_IL(ILID INT PRIMARY KEY,ILName VARCHAR(50))
INSERT INTO T_IL VALUES(1,'IL 1')

HTML

<asp:GridView runat="server" ID="gvMultiple" />
<br />
<asp:GridView runat="server" ID="gvSingle" />

C#

protected void Page_Load(object sender, EventArgs e)
{
    // For multiple record.
    int[] ids = new int[] { 1, 2 }; // Values of selected checkboxes
    TestEntities shy = new TestEntities();
    var rows = (from v in shy.T_Talepler
                join c in shy.T_Klas on v.KlasID equals c.KlasID
                join b in shy.T_Konaklama on v.KonaklamaID equals b.KonaklamaID
                join p in shy.T_IL on v.ILID equals p.ILID
                where ids.Contains(v.ID)
                select new
                {
                    Butce = v.Butce,
                    Donus = v.DonusTarihi,
                    Gidis = v.GidisTarihi,
                    Ilce = p.ILName,
                    Kisi = v.KisiSayisi,
                    Klas = c.KlasName,
                    Talep = v.TalepNo,
                    Konaklama = b.KonaklamaName,
                    TTarih = v.TalepTarihi,
                    klasId = v.KlasID
                }).ToList();
    gvMultiple.DataSource = rows;
    gvMultiple.DataBind();

    // For single record.
    ids = new int[] { 1 }; // Value of selected checkbox
    rows = (from v in shy.T_Talepler
            join c in shy.T_Klas on v.KlasID equals c.KlasID
            join b in shy.T_Konaklama on v.KonaklamaID equals b.KonaklamaID
            join p in shy.T_IL on v.ILID equals p.ILID
            where ids.Contains(v.ID)
            select new
            {
                Butce = v.Butce,
                Donus = v.DonusTarihi,
                Gidis = v.GidisTarihi,
                Ilce = p.ILName,
                Kisi = v.KisiSayisi,
                Klas = c.KlasName,
                Talep = v.TalepNo,
                Konaklama = b.KonaklamaName,
                TTarih = v.TalepTarihi,
                klasId = v.KlasID
            }).ToList();
    gvSingle.DataSource = rows;
    gvSingle.DataBind();
}

Output

For multiple

For Single

Hi Dharmendr,

I try MVC. I try to like below. I get data at the bottom. when I do this I get error "Convert type Generic List to Linq IQueryable" 

 [HttpPost]
        public JsonResult GetList(string klasId)
        {
            var ids = new List<int>();
            IQueryable<T_Talepler> rows = null;

            if (!string.IsNullOrEmpty(klasId))
            {
                for (int i = 0; i < klasId.Split(',').Length; i++)
                {
                    ids.Add(Convert.ToInt32(klasId.Split(',')[i]));
                }


                rows = (from v in shy.T_Talepler
                        join c in shy.T_Klas on v.KlasID equals c.ID
                        join b in shy.T_Konaklama on v.KonaklamaID equals b.ID
                        join p in shy.T_IL on v.ILID equals p.ID
                        where ids.Contains(v.ID)
                        select v).ToList();                      


               
            }
            else
            {
                rows = shy.T_Talepler.Take(5);
            }

            var talep = rows.Select(a => new
            {
                Butce = a.Butce,
                Donus = a.DonusTarihi,
                Gidis = a.GidisTarihi,
                Il = a.T_IL.ILAd,
                Ilce = a.T_ILCE.ILceAd,
                Kisi = a.KisiSayisi,
                Klas = a.T_Klas.UcretTipi,
                Talep = a.TalepNo,
                Konaklama = a.T_Konaklama.KonaklamaGece,
                Konfor = a.T_Konaklama.Konfor,
                TTarih = a.TalepTarihi,
                Sirket = a.T_Sirket.SirketLogo,
                YetkiliAd = a.T_Sirket.YetkiliAd,
                klasId = a.KlasID
            }).ToList();



            return Json(talep, JsonRequestBehavior.AllowGet);

  rows = (from v in shy.T_Talepler
                        join c in shy.T_Klas on v.KlasID equals c.ID
                        join b in shy.T_Konaklama on v.KonaklamaID equals b.ID
                        join p in shy.T_IL on v.ILID equals p.ID
                        where ids.Contains(v.ID)
                        select v).ToList();

AliYilmaz says:

IQueryable<T_Talepler> rows = null;

 Change with the below code.

IQueryable<object> rows = null;

and

select v).ToList();

with

select v).AsQueryable();