how to query in linq to sql such way ?

Question

Hi, I want to select the user. For this user I am marinating the designation Id which is foreign key in user table. as well as I am maintaining the ActiveStatus for soft delete. I want to select the user who are active , but instead of designation Id it must show designation name (like Director, Manager ..so on). How to join 2 tables and get result in linq to sql . I am using ASP.NET + C#.

Answer 1

I suppose you have a designation table with DesignationId and Name, with the DesignationId as a FK to the user table.

from user in users
where user.ActiveStatus == "Active" // or whatever
select new {Name = user.Name, DesignationName = user.Designation.Name}