Left join with Linq to Sql

  • Thread starter Thread starter David
  • Start date Start date
D

David

Hi all,

Still trying to understand Linq at the moment. Parts of the project, I am
still using Sql queries because of my limited knowledge of linq.

A problem I have is doing left joins. I have tried following some examples
on the net but then for some reason, I don't understand how I can get data
out.

I have two tables...

ServiceSheets and TrailerDetails.

In ServiceSheets, I have a SheetID and TrailerID

In TrailerDetails, I have TrailerID and ServiceDueDate

The ServiceSheets is the primary table, I need to left join TrailerDetails.
In Sql, I would do something like...

select * from ServiceSheets left join TrailerDetails on
ServiceSheets.TrailerID = TrailerDetails.TrailerID where
ServiceSheets.SheetID = '123'

The mapping should actually be a 1 to 1 (i.e. only one trailer in
trailerdetails that matches the servicesheet trailer) or 1 to 0 (no trailer
exists yet that matches the servicesheet trailer)

There will only ever be one result from the above SQL, which is based on a
unique SheetID, but there could be more than one sheet in ServiceSheets with
the trailer ID.

My current code looks like... (C#)

using (TrailerDataClassesDataContext dc = new
TrailerDataClassesDataContext())
{
var sheet = from Sheet in dc.ServiceSheets
//join Trailer in dc.TrailerDetails on
Sheet.TrailerID equals Trailer.TrailerID
where Sheet.SheetID == Request.QueryString["sheet"]
select Sheet;

TrailerIDTextBox.Text = sheet.First().TrailerID;
}

As you can see, I have remarked the join out because if I select a sheet
with no trailerdetail, the app stops. (As I understand the join, it is a
right join.) The other samples I found, I couldn't then extract the data
out of the query like I am doing in the sample above.

I need to learn from this, so any help that is given, I would appreciate the
knowledge to understand what is happening in the Linq query.

Thank you for your time.
--
Best regards,
Dave Colliver.
http://www.AshfieldFOCUS.com
~~
http://www.FOCUSPortals.com - Local franchises available
 
var qry = from sheetRow in context.ServiceSheets
join trailerRow in context.TrailerDetails
on sheetRow.TrailerID = trailerRow.TrailerID into joinTable
from result in joinTable.DefaultIfEmpty()
select new
{
Sheet = sheetRow,
Trailer = result
}
 
Sorry, to be more like you example you will want to edit the first line
var qry = from sheetRow in
context.ServiceSheets.Where(c=>c.SheetID==Request.QueryString["sheet"])


SyntaxError said:
var qry = from sheetRow in context.ServiceSheets
join trailerRow in context.TrailerDetails
on sheetRow.TrailerID = trailerRow.TrailerID into joinTable
from result in joinTable.DefaultIfEmpty()
select new
{
Sheet = sheetRow,
Trailer = result
}


David said:
Hi all,

Still trying to understand Linq at the moment. Parts of the project, I am
still using Sql queries because of my limited knowledge of linq.

A problem I have is doing left joins. I have tried following some
examples on the net but then for some reason, I don't understand how I
can get data out.

I have two tables...

ServiceSheets and TrailerDetails.

In ServiceSheets, I have a SheetID and TrailerID

In TrailerDetails, I have TrailerID and ServiceDueDate

The ServiceSheets is the primary table, I need to left join
TrailerDetails. In Sql, I would do something like...

select * from ServiceSheets left join TrailerDetails on
ServiceSheets.TrailerID = TrailerDetails.TrailerID where
ServiceSheets.SheetID = '123'

The mapping should actually be a 1 to 1 (i.e. only one trailer in
trailerdetails that matches the servicesheet trailer) or 1 to 0 (no
trailer exists yet that matches the servicesheet trailer)

There will only ever be one result from the above SQL, which is based on
a unique SheetID, but there could be more than one sheet in ServiceSheets
with the trailer ID.

My current code looks like... (C#)

using (TrailerDataClassesDataContext dc = new
TrailerDataClassesDataContext())
{
var sheet = from Sheet in dc.ServiceSheets
//join Trailer in dc.TrailerDetails on
Sheet.TrailerID equals Trailer.TrailerID
where Sheet.SheetID ==
Request.QueryString["sheet"]
select Sheet;

TrailerIDTextBox.Text = sheet.First().TrailerID;
}

As you can see, I have remarked the join out because if I select a sheet
with no trailerdetail, the app stops. (As I understand the join, it is a
right join.) The other samples I found, I couldn't then extract the data
out of the query like I am doing in the sample above.

I need to learn from this, so any help that is given, I would appreciate
the knowledge to understand what is happening in the Linq query.

Thank you for your time.
--
Best regards,
Dave Colliver.
http://www.AshfieldFOCUS.com
~~
http://www.FOCUSPortals.com - Local franchises available
Click to expand...
Click to expand...
 
Hi,

Used that query and modified my Textbox to...

FleetIDTextBox.Text = qry.First().Trailer.TrailerID;

I am now getting an Object reference not set to an instance of an object,
and this is one where there is a trailer id.

When I change it to qry.First().Sheet.TrailerID; it runs, but I get nothing
out.

This qry generates...

SELECT [t0].[ID], [t0].[SheetID], [t0].[SavePath], [t0].[TrailerID],
[t0].[Barcode], [t0].[Received], [t2].[test], [t2].[id] AS [id2],
[t2].[TrailerID] AS [TrailerID2], [t2].[Barcode] AS [Barcode2],
[t2].[ServiceDue]
FROM [dbo].[ServiceSheets] AS [t0]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t1].[id], [t1].[TrailerID], [t1].[Barcode],
[t1].[ServiceDue]
FROM [dbo].[TrailerDetails] AS [t1]
) AS [t2] ON [t0].[TrailerID] = [t2].[TrailerID]


Ah, just realised... the where clause was missing, which in my test data,
the first trailerid happens to be null. It is now running properly if I use
Sheet.TrailerID (which is actually what it should be.)

I had seen that selecting into another table before, but I just couldn't get
it to work.

Thanks for your help.

--
Best regards,
Dave Colliver.
http://www.AshfieldFOCUS.com
~~
http://www.FOCUSPortals.com - Local franchises available
SyntaxError said:
var qry = from sheetRow in context.ServiceSheets
join trailerRow in context.TrailerDetails
on sheetRow.TrailerID = trailerRow.TrailerID into joinTable
from result in joinTable.DefaultIfEmpty()
select new
{
Sheet = sheetRow,
Trailer = result
}


David said:
Hi all,

Still trying to understand Linq at the moment. Parts of the project, I am
still using Sql queries because of my limited knowledge of linq.

A problem I have is doing left joins. I have tried following some
examples on the net but then for some reason, I don't understand how I
can get data out.

I have two tables...

ServiceSheets and TrailerDetails.

In ServiceSheets, I have a SheetID and TrailerID

In TrailerDetails, I have TrailerID and ServiceDueDate

The ServiceSheets is the primary table, I need to left join
TrailerDetails. In Sql, I would do something like...

select * from ServiceSheets left join TrailerDetails on
ServiceSheets.TrailerID = TrailerDetails.TrailerID where
ServiceSheets.SheetID = '123'

The mapping should actually be a 1 to 1 (i.e. only one trailer in
trailerdetails that matches the servicesheet trailer) or 1 to 0 (no
trailer exists yet that matches the servicesheet trailer)

There will only ever be one result from the above SQL, which is based on
a unique SheetID, but there could be more than one sheet in ServiceSheets
with the trailer ID.

My current code looks like... (C#)

using (TrailerDataClassesDataContext dc = new
TrailerDataClassesDataContext())
{
var sheet = from Sheet in dc.ServiceSheets
//join Trailer in dc.TrailerDetails on
Sheet.TrailerID equals Trailer.TrailerID
where Sheet.SheetID ==
Request.QueryString["sheet"]
select Sheet;

TrailerIDTextBox.Text = sheet.First().TrailerID;
}

As you can see, I have remarked the join out because if I select a sheet
with no trailerdetail, the app stops. (As I understand the join, it is a
right join.) The other samples I found, I couldn't then extract the data
out of the query like I am doing in the sample above.

I need to learn from this, so any help that is given, I would appreciate
the knowledge to understand what is happening in the Linq query.

Thank you for your time.
--
Best regards,
Dave Colliver.
http://www.AshfieldFOCUS.com
~~
http://www.FOCUSPortals.com - Local franchises available
Click to expand...
Click to expand...
 
Further to my last...

I am trying to pick off the ServiceDueDate which is in the child table.
However, if the child has no data (null), then I am getting an error. I have
tried to trap it, but no joy...

How should I do this?

if (sheet.First().Trailer.ServiceDue.HasValue)
{
LastInspectionLabel.Text =
sheet.First().Trailer.ServiceDue.ToString();
}

(ServiceDue is a DateTime type)

--
Best regards,
Dave Colliver.
http://www.AshfieldFOCUS.com
~~
http://www.FOCUSPortals.com - Local franchises available
David said:
Hi,

Used that query and modified my Textbox to...

FleetIDTextBox.Text = qry.First().Trailer.TrailerID;

I am now getting an Object reference not set to an instance of an object,
and this is one where there is a trailer id.

When I change it to qry.First().Sheet.TrailerID; it runs, but I get
nothing out.

This qry generates...

SELECT [t0].[ID], [t0].[SheetID], [t0].[SavePath], [t0].[TrailerID],
[t0].[Barcode], [t0].[Received], [t2].[test], [t2].[id] AS [id2],
[t2].[TrailerID] AS [TrailerID2], [t2].[Barcode] AS [Barcode2],
[t2].[ServiceDue]
FROM [dbo].[ServiceSheets] AS [t0]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t1].[id], [t1].[TrailerID], [t1].[Barcode],
[t1].[ServiceDue]
FROM [dbo].[TrailerDetails] AS [t1]
) AS [t2] ON [t0].[TrailerID] = [t2].[TrailerID]


Ah, just realised... the where clause was missing, which in my test data,
the first trailerid happens to be null. It is now running properly if I
use Sheet.TrailerID (which is actually what it should be.)

I had seen that selecting into another table before, but I just couldn't
get it to work.

Thanks for your help.

--
Best regards,
Dave Colliver.
http://www.AshfieldFOCUS.com
~~
http://www.FOCUSPortals.com - Local franchises available
SyntaxError said:
var qry = from sheetRow in context.ServiceSheets
join trailerRow in context.TrailerDetails
on sheetRow.TrailerID = trailerRow.TrailerID into joinTable
from result in joinTable.DefaultIfEmpty()
select new
{
Sheet = sheetRow,
Trailer = result
}


David said:
Hi all,

Still trying to understand Linq at the moment. Parts of the project, I
am still using Sql queries because of my limited knowledge of linq.

A problem I have is doing left joins. I have tried following some
examples on the net but then for some reason, I don't understand how I
can get data out.

I have two tables...

ServiceSheets and TrailerDetails.

In ServiceSheets, I have a SheetID and TrailerID

In TrailerDetails, I have TrailerID and ServiceDueDate

The ServiceSheets is the primary table, I need to left join
TrailerDetails. In Sql, I would do something like...

select * from ServiceSheets left join TrailerDetails on
ServiceSheets.TrailerID = TrailerDetails.TrailerID where
ServiceSheets.SheetID = '123'

The mapping should actually be a 1 to 1 (i.e. only one trailer in
trailerdetails that matches the servicesheet trailer) or 1 to 0 (no
trailer exists yet that matches the servicesheet trailer)

There will only ever be one result from the above SQL, which is based on
a unique SheetID, but there could be more than one sheet in
ServiceSheets with the trailer ID.

My current code looks like... (C#)

using (TrailerDataClassesDataContext dc = new
TrailerDataClassesDataContext())
{
var sheet = from Sheet in dc.ServiceSheets
//join Trailer in dc.TrailerDetails on
Sheet.TrailerID equals Trailer.TrailerID
where Sheet.SheetID ==
Request.QueryString["sheet"]
select Sheet;

TrailerIDTextBox.Text = sheet.First().TrailerID;
}

As you can see, I have remarked the join out because if I select a sheet
with no trailerdetail, the app stops. (As I understand the join, it is a
right join.) The other samples I found, I couldn't then extract the
data out of the query like I am doing in the sample above.

I need to learn from this, so any help that is given, I would appreciate
the knowledge to understand what is happening in the Linq query.

Thank you for your time.
--
Best regards,
Dave Colliver.
http://www.AshfieldFOCUS.com
~~
http://www.FOCUSPortals.com - Local franchises available
Click to expand...
Click to expand...
Click to expand...
 
LastInspectionLabel.Text = sheet.First().Trailer == null ? string.Empty :
sheet.First().Trailer.ServiceDue.ToString("d");


David said:
Further to my last...

I am trying to pick off the ServiceDueDate which is in the child table.
However, if the child has no data (null), then I am getting an error. I
have tried to trap it, but no joy...

How should I do this?

if (sheet.First().Trailer.ServiceDue.HasValue)
{
LastInspectionLabel.Text =
sheet.First().Trailer.ServiceDue.ToString();
}

(ServiceDue is a DateTime type)

--
Best regards,
Dave Colliver.
http://www.AshfieldFOCUS.com
~~
http://www.FOCUSPortals.com - Local franchises available
David said:
Hi,

Used that query and modified my Textbox to...

FleetIDTextBox.Text = qry.First().Trailer.TrailerID;

I am now getting an Object reference not set to an instance of an object,
and this is one where there is a trailer id.

When I change it to qry.First().Sheet.TrailerID; it runs, but I get
nothing out.

This qry generates...

SELECT [t0].[ID], [t0].[SheetID], [t0].[SavePath], [t0].[TrailerID],
[t0].[Barcode], [t0].[Received], [t2].[test], [t2].[id] AS [id2],
[t2].[TrailerID] AS [TrailerID2], [t2].[Barcode] AS [Barcode2],
[t2].[ServiceDue]
FROM [dbo].[ServiceSheets] AS [t0]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t1].[id], [t1].[TrailerID], [t1].[Barcode],
[t1].[ServiceDue]
FROM [dbo].[TrailerDetails] AS [t1]
) AS [t2] ON [t0].[TrailerID] = [t2].[TrailerID]


Ah, just realised... the where clause was missing, which in my test data,
the first trailerid happens to be null. It is now running properly if I
use Sheet.TrailerID (which is actually what it should be.)

I had seen that selecting into another table before, but I just couldn't
get it to work.

Thanks for your help.

--
Best regards,
Dave Colliver.
http://www.AshfieldFOCUS.com
~~
http://www.FOCUSPortals.com - Local franchises available
SyntaxError said:
var qry = from sheetRow in context.ServiceSheets
join trailerRow in context.TrailerDetails
on sheetRow.TrailerID = trailerRow.TrailerID into joinTable
from result in joinTable.DefaultIfEmpty()
select new
{
Sheet = sheetRow,
Trailer = result
}


Hi all,

Still trying to understand Linq at the moment. Parts of the project, I
am still using Sql queries because of my limited knowledge of linq.

A problem I have is doing left joins. I have tried following some
examples on the net but then for some reason, I don't understand how I
can get data out.

I have two tables...

ServiceSheets and TrailerDetails.

In ServiceSheets, I have a SheetID and TrailerID

In TrailerDetails, I have TrailerID and ServiceDueDate

The ServiceSheets is the primary table, I need to left join
TrailerDetails. In Sql, I would do something like...

select * from ServiceSheets left join TrailerDetails on
ServiceSheets.TrailerID = TrailerDetails.TrailerID where
ServiceSheets.SheetID = '123'

The mapping should actually be a 1 to 1 (i.e. only one trailer in
trailerdetails that matches the servicesheet trailer) or 1 to 0 (no
trailer exists yet that matches the servicesheet trailer)

There will only ever be one result from the above SQL, which is based
on a unique SheetID, but there could be more than one sheet in
ServiceSheets with the trailer ID.

My current code looks like... (C#)

using (TrailerDataClassesDataContext dc = new
TrailerDataClassesDataContext())
{
var sheet = from Sheet in dc.ServiceSheets
//join Trailer in dc.TrailerDetails on
Sheet.TrailerID equals Trailer.TrailerID
where Sheet.SheetID ==
Request.QueryString["sheet"]
select Sheet;

TrailerIDTextBox.Text = sheet.First().TrailerID;
}

As you can see, I have remarked the join out because if I select a
sheet with no trailerdetail, the app stops. (As I understand the join,
it is a right join.) The other samples I found, I couldn't then
extract the data out of the query like I am doing in the sample above.

I need to learn from this, so any help that is given, I would
appreciate the knowledge to understand what is happening in the Linq
query.

Thank you for your time.
--
Best regards,
Dave Colliver.
http://www.AshfieldFOCUS.com
~~
http://www.FOCUSPortals.com - Local franchises available
Click to expand...
Click to expand...
Click to expand...
 
Cool, that works, thank you.

--
Best regards,
Dave Colliver.
http://www.AshfieldFOCUS.com
~~
http://www.FOCUSPortals.com - Local franchises available


SyntaxError said:
LastInspectionLabel.Text = sheet.First().Trailer == null ? string.Empty :
sheet.First().Trailer.ServiceDue.ToString("d");


David said:
Further to my last...

I am trying to pick off the ServiceDueDate which is in the child table.
However, if the child has no data (null), then I am getting an error. I
have tried to trap it, but no joy...

How should I do this?

if (sheet.First().Trailer.ServiceDue.HasValue)
{
LastInspectionLabel.Text =
sheet.First().Trailer.ServiceDue.ToString();
}

(ServiceDue is a DateTime type)

--
Best regards,
Dave Colliver.
http://www.AshfieldFOCUS.com
~~
http://www.FOCUSPortals.com - Local franchises available
David said:
Hi,

Used that query and modified my Textbox to...

FleetIDTextBox.Text = qry.First().Trailer.TrailerID;

I am now getting an Object reference not set to an instance of an
object, and this is one where there is a trailer id.

When I change it to qry.First().Sheet.TrailerID; it runs, but I get
nothing out.

This qry generates...

SELECT [t0].[ID], [t0].[SheetID], [t0].[SavePath], [t0].[TrailerID],
[t0].[Barcode], [t0].[Received], [t2].[test], [t2].[id] AS [id2],
[t2].[TrailerID] AS [TrailerID2], [t2].[Barcode] AS [Barcode2],
[t2].[ServiceDue]
FROM [dbo].[ServiceSheets] AS [t0]
LEFT OUTER JOIN (
SELECT 1 AS [test], [t1].[id], [t1].[TrailerID], [t1].[Barcode],
[t1].[ServiceDue]
FROM [dbo].[TrailerDetails] AS [t1]
) AS [t2] ON [t0].[TrailerID] = [t2].[TrailerID]


Ah, just realised... the where clause was missing, which in my test
data, the first trailerid happens to be null. It is now running properly
if I use Sheet.TrailerID (which is actually what it should be.)

I had seen that selecting into another table before, but I just couldn't
get it to work.

Thanks for your help.

--
Best regards,
Dave Colliver.
http://www.AshfieldFOCUS.com
~~
http://www.FOCUSPortals.com - Local franchises available
var qry = from sheetRow in context.ServiceSheets
join trailerRow in context.TrailerDetails
on sheetRow.TrailerID = trailerRow.TrailerID into joinTable
from result in joinTable.DefaultIfEmpty()
select new
{
Sheet = sheetRow,
Trailer = result
}


Hi all,

Still trying to understand Linq at the moment. Parts of the project, I
am still using Sql queries because of my limited knowledge of linq.

A problem I have is doing left joins. I have tried following some
examples on the net but then for some reason, I don't understand how I
can get data out.

I have two tables...

ServiceSheets and TrailerDetails.

In ServiceSheets, I have a SheetID and TrailerID

In TrailerDetails, I have TrailerID and ServiceDueDate

The ServiceSheets is the primary table, I need to left join
TrailerDetails. In Sql, I would do something like...

select * from ServiceSheets left join TrailerDetails on
ServiceSheets.TrailerID = TrailerDetails.TrailerID where
ServiceSheets.SheetID = '123'

The mapping should actually be a 1 to 1 (i.e. only one trailer in
trailerdetails that matches the servicesheet trailer) or 1 to 0 (no
trailer exists yet that matches the servicesheet trailer)

There will only ever be one result from the above SQL, which is based
on a unique SheetID, but there could be more than one sheet in
ServiceSheets with the trailer ID.

My current code looks like... (C#)

using (TrailerDataClassesDataContext dc = new
TrailerDataClassesDataContext())
{
var sheet = from Sheet in dc.ServiceSheets
//join Trailer in dc.TrailerDetails on
Sheet.TrailerID equals Trailer.TrailerID
where Sheet.SheetID ==
Request.QueryString["sheet"]
select Sheet;

TrailerIDTextBox.Text = sheet.First().TrailerID;
}

As you can see, I have remarked the join out because if I select a
sheet with no trailerdetail, the app stops. (As I understand the join,
it is a right join.) The other samples I found, I couldn't then
extract the data out of the query like I am doing in the sample above.

I need to learn from this, so any help that is given, I would
appreciate the knowledge to understand what is happening in the Linq
query.

Thank you for your time.
--
Best regards,
Dave Colliver.
http://www.AshfieldFOCUS.com
~~
http://www.FOCUSPortals.com - Local franchises available
Click to expand...
Click to expand...
Click to expand...
 
Back
Top