CODEWITHSUNDEEP
c#sql-serverlinq
Akib Ali
Akib Ali

Reputation: 23

SQL to Linq for inner and left join together

My sql query is

SELECT T.TicketId,
         T.Subject,
         T.Issue,
         T.Priority,
         T.StatusId,
         T.Attachment,
         T.CreatedDate,
         T.ModifiedDate,
         T.Comment,
         U.Username as CreatedBy,
         TU.Username as Assigned
  FROM   tblTicket T
         INNER JOIN tblUser U
                    ON T.CreatedBy = U.UserId
         LEFT JOIN tblUser TU
                    ON T.AssignTo = TU.UserId

And I have tried to convert it in linq by below query

from ticket in tblTickets
join user in tblUsers
       on     ticket.CreatedBy equals user.UserId
into temp from tblUsers in temp.DefaultIfEmpty()
select new
{
       ticket.TicketId,
       ticket.Subject,
       ticket.Issue,
       ticket.Priority,
       ticket.StatusId,
       ticket.Attachment,
       ticket.CreatedDate,
       ticket.ModifiedDate,
       ticket.Comment,
       user.Username
}

And I am getting below error in linq pad

The range variable 'tblUsers' conflicts with a previous declaration of 'tblUsers'

I know I am repeating the table for the left but variable name user and temp I mean not same so why I am getting this error and what is correct linq query for it.

Upvotes: 2

Views: 118

Answers (3)

cleftheris
cleftheris

Reputation: 4839

you are reusing the same variable name in your Linq2SQL query as the error suggests.

Take notice that in the Linq syntax from item in table and join item2 in table2 ... both the item & item2 are a new variable declarations.

Something like this should fix it. See how I replaced the second use of the tblUsers with a new variable userAssignedLeft.

from ticket in tblTickets
join user in tblUsers on ticket.CreatedBy equals user.UserId
join userAssigned in tblUsers on ticket.AssignTo equals userAssigned.UserId into temp 
from userAssignedLeft in temp.DefaultIfEmpty()
select new
{
       ticket.TicketId,
       ticket.Subject,
       ticket.Issue,
       ticket.Priority,
       ticket.StatusId,
       ticket.Attachment,
       ticket.CreatedDate,
       ticket.ModifiedDate,
       ticket.Comment,
       UsernameCreatedBy = user.Username,
       UsernameAssigned = userAssignedLeft.Username
}

Upvotes: 1

pradeep varma
pradeep varma

Reputation: 136

variable 'tbluser' is already used in that query only, that why getting error.

from ticket in tblTickets
join user in tblUsers on ticket.CreatedBy equals user.UserId into temp 
from userLeft in temp.DefaultIfEmpty()
select new
{
   ticket.TicketId,
   ticket.Subject,
   ticket.Issue,
   ticket.Priority,
   ticket.StatusId,
   ticket.Attachment,
   ticket.CreatedDate,
   ticket.ModifiedDate,
   ticket.Comment,
  (userLeft == null ? String.Empty : userLeft.Username)
}

right table may contain null. so, it is good to check null for right table

Upvotes: 0

Pomme De Terre
Pomme De Terre

Reputation: 372

Try this :

from ticket in tblTickets
join user in tblUsers
       on ticket.CreatedBy equals user.UserId
into temp from user  in temp.DefaultIfEmpty()
select new
{
     ticket.TicketId,
     ticket.Subject,
     ticket.Issue,
     ticket.Priority,
     ticket.StatusId,
     ticket.Attachment,
     ticket.CreatedDate,
     ticket.ModifiedDate,
     ticket.Comment,
     temp.Username
}

Upvotes: 0

Related Questions