CODEWITHSUNDEEP
c#asp.netsql-serverlinqjoin
Ahmer Ali Ahsan
Ahmer Ali Ahsan

Reputation: 6136

How to convert sql inner join query into linq to sql query and convert into list

I have below class:

public class customRequest
{
    public string REQ_STATUS { get; set; }
    public DateTime REQ_RVE_DATnTIM { get; set; }
    public string EMP_FNAME { get; set; }
    public string EVE_CAT_NAME { get; set; }
    public string EVE_NAME { get; set; }
}

and below SQL Inner Join Query which works perfect in SQL:

SELECT req.REQ_STATUS,req.REQ_RVE_DATnTIM, emp.EMP_FNAME, cat.EVE_CAT_NAME,eve.EVE_NAME from REQUESTS req inner join Employees emp on req.REQ_EMP_ID = emp.ID inner join EVENTCATEGORIES cat on req.REQ_EVE_CAT_ID = cat.ID inner join EVENTS eve on req.REQ_EVE_NAM_ID = eve.ID order by req.REQ_STATUS

What I want is to convert it into my linq to sql query. Below is my Linq to Sql query which I tried:

slist = from req in db.REQUESTS
                    join emp in db.Employees on req.REQ_EMP_ID equals emp.ID
                    join cat in db.EVENTCATEGORIES on req.REQ_EVE_CAT_ID equals cat.ID
                    join eve in db.EVENTS on req.REQ_EVE_NAM_ID equals eve.ID
                    select new { req.REQ_STATUS, req.REQ_RVE_DATnTIM, emp.EMP_FNAME, cat.EVE_CAT_NAME, eve.EVE_NAME };
        return slist.ToList();

But it shows me an error on last join:

Error 1 Cannot implicitly convert type 'System.Linq.IQueryable' to 'System.Collections.Generic.List'. An explicit conversion exists (are you missing a cast?) H:\64bit\Project Finalizing\Blue_Pumpkin\Blue_Pumpkin\Admin\Requests.aspx.cs 32 25 Blue_Pumpkin

Upvotes: 1

Views: 1679

Answers (2)

Sahi
Sahi

Reputation: 1484

You can use ToList() along with your linq query :

var slist = (from req in db.REQUESTS
                join emp in db.Employees on req.REQ_EMP_ID equals emp.ID
                join cat in db.EVENTCATEGORIES on req.REQ_EVE_CAT_ID equals cat.ID
                join eve in db.EVENTS on req.REQ_EVE_NAM_ID equals eve.ID
                select new select new customRequest {
                   REQ_STATUS = req.REQ_STATUS,
                   REQ_RVE_DATnTIM= req.REQ_RVE_DATnTIM, 
                   EMP_FNAME = emp.EMP_FNAME, 
                   EVE_CAT_NAME = cat.EVE_CAT_NAME, 
                   EVE_NAME = eve.EVE_NAME }){
                   REQ_STATUS = req.REQ_STATUS, req.REQ_RVE_DATnTIM, 
                   EMP_FNAME = emp.EMP_FNAME, 
                   EVE_CAT_NAME = cat.EVE_CAT_NAME, 
                   EVE_NAME = eve.EVE_NAME }).ToList();

Upvotes: 0

James Joyce Alano
James Joyce Alano

Reputation: 703

I'm assuming you wish to return a customRequest class from your query. You need to explicitly cast your select statement as customRequest.. 

slist = (from req in db.REQUESTS
                    join emp in db.Employees on req.REQ_EMP_ID equals emp.ID
                    join cat in db.EVENTCATEGORIES on req.REQ_EVE_CAT_ID equals cat.ID
                    join eve in db.EVENTS on req.REQ_EVE_NAM_ID equals eve.ID
                    select new customRequest {
                       REQ_STATUS = req.REQ_STATUS, req.REQ_RVE_DATnTIM, 
                       EMP_FNAME = emp.EMP_FNAME, 
                       EVE_CAT_NAME = cat.EVE_CAT_NAME, 
                       EVE_NAME = eve.EVE_NAME }).ToList();
            return slist;

If it so happens that you do need to return a list.. You need to declare your return type as a list variable and use ToList() from there, if you are to return a single row result set then you can use First() or FirstOrDefault()

Upvotes: 3

Related Questions