C# LINQ Join Error

Question

SELECT     PART_TYPE.PART_TYPE_ID,
           PART_TYPE.PART_TYPE_NAME,                       
           PART_AVAILABILITY.DATE_REF, 
           PART_TYPE.VEHICLE_ID,
           PART_AVAILABILITY.AVAIL_COUNT

FROM  PART_AVAILABILITY
RIGHT JOIN PART_TYPE
ON PART_AVAILABILITY.PART_TYPE_ID = PART_TYPE.PART_TYPE_ID
AND PART_AVAILABILITY.VEHICLE_ID = PART_TYPE.VEHICLE_ID 
where PART_TYPE.VEHICLE_ID = 366

PART_TYPE_ID and VEHICLE_ID are Primary Key in PART_TYPE table. VEHICLE_ID is a Foreign Key from VEHICLE table.

DATE_REF, PART_TYPE_ID and VEHICLE_ID are Primary Key in PART_AVAILABILITY table. VEHICLE_ID and PART_TYPE_ID are Foreign Key from PART_TYPE table.

Above query gave below output.

PART_TYPE_ID    PART_TYPE_NAME  DATE_REF                 VEHICLE_ID  AVAIL_COUNT
5               A1              2013-06-20 00:00:00.000  366         1
6               B1              2013-06-20 00:00:00.000  366         2
7               C1              2013-06-20 00:00:00.000  366         1
8               D1              NULL                     366         NULL
9               E1              NULL                     366         NULL
16              F1              2013-06-20 00:00:00.000  366         1

This my linq query for above sql query.

var vehiclePartType = from pa in context.PART_AVAILABILITY
                    join pt in context.PART_TYPE
                    on pa.PART_TYPE_ID equals pt.PART_TYPE_ID into joined
                    from j in joined.DefaultIfEmpty()
                    where j.VEHICLE_ID == 366
                    select new
                    {
                        PART_TYPE = j,
                        PART_AVAILABILITY = pa
                    };

But linq query gave below output.

PART_TYPE_ID    PART_TYPE_NAME  DATE_REF                 VEHICLE_ID  AVAIL_COUNT
5               A1              2013-06-20 00:00:00.000  366         1
6               B1              2013-06-20 00:00:00.000  366         2
7               C1              2013-06-20 00:00:00.000  366         1
16              F1              2013-06-20 00:00:00.000  366         1

NULL records are missing.

How can i solve this ?

gzaxx · Accepted Answer · 2013-06-25 07:28:15Z

3

Right Join in LINQ is done by reversing join statements so correct one would be this:

var vehiclePartType = from pt in context.PART_TYPE
                      join pa in context.PART_AVAILABILITY on pt.PART_TYPE_ID equals pa.PART_TYPE_ID into joined
                      from j in joined.DefaultIfEmpty()
                      where pt.VEHICLE_ID == 366
                      select new
                      {
                          PART_TYPE = pt,
                          PART_AVAILABILITY = j
                      };

edited Jun 25, 2013 at 7:28

answered Jun 25, 2013 at 7:01

gzaxx

17.6k2 gold badges40 silver badges55 bronze badges

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

7 Comments

Bishan Over a year ago

Getting error as The name 'pa' does not exist in the current context from PART_AVAILABILITY = pa in Select

gzaxx Over a year ago

Fixed, please try again :)

Bishan Over a year ago

no. i have tried this before. this is also return output in my question. NULL records are missing.

gzaxx Over a year ago

Okay, now it should be fixed. Changed where clause as that was what filtered out nulls :)

Bishan Over a year ago

Thanks :). I want to filter result with DATE_REF in PART_AVAILABILITY table. so i added line as && j.DATE_REF == dt after where clause. (dt is DateTime object). then again NULL records are missing. what is the issue ?

|

Prince · Accepted Answer · 2013-06-25 07:01:54Z

1

I think You have to swap your tables. Actually what you are doing is left join . To convert into right just swap the tables Like::

  {
                      var vehiclePartType = from 
                        pt in context.PART_TYPE join pa in context.PART_AVAILABILITY
                        on pt.PART_TYPE_ID equals pa.PART_TYPE_ID   into joined
                        from j in joined.DefaultIfEmpty()
                        where j.VEHICLE_ID == 366
                        select new
                        {
                            PART_TYPE = j,
                            PART_AVAILABILITY = pa
                        };
    }

answered Jun 25, 2013 at 7:01

Prince

3381 gold badge2 silver badges11 bronze badges

1 Comment

Bishan Over a year ago

Getting error as The name 'pa' does not exist in the current context from PART_AVAILABILITY = pa in Select

Microsoft DN · Accepted Answer · 2013-06-25 06:59:16Z

0

I am not too much familier with LINQ but in your SQL query you have

AND PART_AVAILABILITY.VEHICLE_ID = PART_TYPE.VEHICLE_ID

This is missing in your LINQ query.

answered Jun 25, 2013 at 6:59

Microsoft DN

10.1k10 gold badges54 silver badges74 bronze badges

1 Comment

Bishan Over a year ago

SQL query returns above data with out that part. so that is not the issue.

Collectives™ on Stack Overflow

C# LINQ Join Error

3 Answers 3

7 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related