CODEWITHSUNDEEP
c#sqllinqlambda
Alexis Lopez
Alexis Lopez

Reputation: 53

How to use Inner Join with Lambda expression?

I have a query in SQL that need convert to lambda expression, but I'm not sure how to use the WHERE function.

This is my query in SQL:

SELECT * FROM tickets t INNER JOIN groups g on t.GroupId = g.GroupId WHERE g.UserId = 4 AND t.StatusId = 3 OR t.UserCreatedBy = 4 AND t.StatusId = 3

I tried to convert it of the following way:

var query = db.Tickets
.Include(t => t.CftGroup)
.Include(t => t.Customer)
.Include(t => t.Factory)
.Include(t => t.Failure)
.Include(t => t.Line)
.Include(t => t.Priority)
.Include(t => t.Process.ProcessGroup)
.Include(t => t.Site)
.Include(t => t.Status)
.Join(db.Groups, t => t.CftGroupId, g => g.CftGroupId, (t, g) => new
{
    userCreatedBy = t.UserCreatedBy,
    userId = g.UserId,
    statusId = t.StatusId
})
.Where(
x => x.userId == id
&& x.statusId != Closed
|| x.userCreatedBy = id
&& x.statusId != Closed
);

Upvotes: 0

Views: 284

Answers (1)

Wendel Viana
Wendel Viana

Reputation: 26

These worked fine with me:

  var TicketList =   Tickets
    .Join( Groups,
         ticket => ticket.GroupID,
         group => group.ID,
         (customer , group ) => new { Ticket = ticket, Group = group})
.Where(x => x.Group.UserId == 4 && (x.Ticket.StatusId == 3 || x.Ticket.UserCreatedBy == 4) && x.Ticket.StatusId == 3)
         .Select(select => select.Ticket ).ToList();

Upvotes: 1

Related Questions