How to use Inner Join with Lambda expression?

Question

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
);

Why does your LINQ have x.statusID != Closed but your SQL have t.StatusId = 3? — NetMage
– NetMage, Commented Jul 24, 2019 at 19:22

Wendel Viana · Accepted Answer · 2019-07-24 19:28:24Z

1

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();

answered Jul 24, 2019 at 19:28

Wendel Viana

262 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use Inner Join with Lambda expression?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related