CODEWITHSUNDEEP
c#asp.net-mvclinq
kojirosan
kojirosan

Reputation: 507

Linq How to Join and get 2 tables values

I have 2 tables and i want to match up 2 Id values.

First table

Second table

I want to match up first table's Id's with second table's Id's so i can get DepartmentId values.

I need to get this virtual result:

Here is my code:

       for (int i = 0; i < model1.Count(); i++)
    { 
model1[i].DepartmentId= model2.FirstOrDefault(k => k.Id== model1[i].Id).DepartmentId; 
      }

I get this error:

An exception of type 'System.NullReferenceException' occurred in IYP.UserInterfaceLayer.dll but was not handled in user code

I think loop fails because of it can't find 10, 30, 40 Id values. If my Id values are same in 2 tables( Id = 1,2,3,4,5) loop works.

How can i do this with Linq?

Upvotes: 1

Views: 1137

Answers (4)

Rahul Singh
Rahul Singh

Reputation: 21795

You are basically looking for Left Join in LINQ. Try this:-

 var query = from emp2 in Employee2
                        join emp1 in Employee1
                        on emp2.Id equals emp1.Id into allEmployees
                        from result in allEmployees.DefaultIfEmpty()
                        select new
                        {
                            ID = emp2.Id,
                            DeptID = result == null ? "No Department" : result.DepartmentId.ToString()
                        };

Where I have used following types:-

          var Employee1 = new[]
            {
                new { Id = 1, DepartmentId  = 2 },
                new { Id = 2, DepartmentId  = 4 },
                new { Id = 3, DepartmentId  = 5 },
                new { Id = 4, DepartmentId  = 2 },
                new { Id = 5, DepartmentId  = 1 },
            };

        var Employee2 = new[] 
        {
            new { Id = 1 },
            new { Id = 2 },
            new { Id = 10 },
            new { Id = 30 },
            new { Id = 40 },
        };

Complete Working Fiddle.

Upvotes: 2

Phoenix
Phoenix

Reputation: 913

I am going to assume that model1 and model2 are both IEnumerable. In that case the following should work.

var result = from x in model2
select
   new Model1Type {DepartamentId = x,
Value=
model1.FirstOrDefault(y=>y.DepartamentId==x)
.Select(y=>y.Value)};

This is called Lamq :D Hope this helps :)

Upvotes: 1

user4093832
user4093832

Reputation:

try this

List<long> idlist=model2.tolist().select(t=>t.Id);
List<long> depIdList=model1.where(t=>idlist.contains(t.id)).toList();

Upvotes: 1

galenus
galenus

Reputation: 2127

You should use the Join LINQ extension method. In the form of query syntax (which I believe is more readable for this case) it will look like:

var matchedValues =
    from second in model2
    join first in model1
        on second.Id equals first.Id
        into temp
    from tempFirst in temp.DefaultIfEmpty()
    select
        new 
        {
            second.Id,
            DepartmentId = tempFirst == null ? null : tempFirst.DepartmentId
        };

You join on the Id property and for any value you don't find in the model1, you use a default (DefaultIfEmpty call). Then you choose the resulting DepartmentId based on the join result.

Upvotes: 1

Related Questions