CODEWITHSUNDEEP
c#linq
gnivler
gnivler

Reputation: 100

LINQ query to return from one table based on matching another table

I'm very novice with C# and LINQ and have been unable to design the query and/or code necessary. I've researched here, Microsoft docs, C# in a Nutshell and have learned a lot but can't seem to apply it properly.

The data source is SQL Express, 5 small simple tables. I wound up with a bit of a progressive design to clarify for myself. I have MeetingDetail type that has a property int PartnerID, which is related to the Employees table Employee.ID -> MeetingDetail.PartnerID. I want my new type to contain a property of the name of the partner based on this match. The source properties are db.Employees.LastName and FirstName, both String type.

var filtered = db.MeetingDetails.Where(v => v.GroupID == ID);

var recentMeetingRowQuery = from meeting in filtered
select new
{
    Date = meeting.MeetingDate,
    Category = meeting.Category.Name,
    Partner = (db.Employees.Where(v => v.ID == meeting.PartnerID).Select(x => x.LastName))
};

It seems this is returning the wrong type for Partner property and I've tried ToString() in various ways.

enter image description here

Upvotes: 2

Views: 2212

Answers (1)

Sergey Berezovskiy
Sergey Berezovskiy

Reputation: 236188

Use join to match records from two tables:

var recentMeetingRowQuery = 
       from meeting in db.MeetingDetails.Where(m => m.GroupID == ID)
       join employee in db.Employees on meeting.PartnerID equals employee.ID 
       select new {
           Date = meeting.MeetingDate,
           Category = meeting.Category.Name,
           Partner = employee.LastName
       };

Note: I assume you are using employee ID for matching and v => ID == meeting.PartnerID should actually be v => v.ID == meeting.PartnerID.

It seems this is returning the wrong type for Partner property

Because Select(x => x.LastName) returns IQueryable<string>. You could select only first matched employee name by adding .FirstOrDefault() but join is more efficient solution.

Upvotes: 3

Related Questions