Full outer join with LINQ to Dataset example (work around)
Hi,
Base from what I am reading about LINQ Full outher join is not supported, and I could not really find a good example of a workaround for outer join in LINQ to Dataset.
Here is an example I would like to see working with LINQ to Dataset
Let's start with SQL server side with two tables
Table 1
CREATE TABLE [dbo].[ttsls](
[supp_inv_id] [int] NULL,
[family_id] [int] NULL,
[amount] [int] NULL
) ON [PRIMARY]
Table 2
CREATE TABLE [dbo].[imaging](
[family_id] [int] NOT NULL,
[image_name] [varchar](50) NULL
) ON [PRIMARY]
Here is the data in both tables
Table 1:
supp_inv_id family_id amount
----------- --------------------------
1 11 10
2 12 5
3 13 5
4 NULL 7
5 NULL 9
Table 2:
family_id image_name
----------- --------------------------
11 image1
12 image2
13 image3
16 image3
17 image4
Now I want to make a Full Join between these two tables with LINQ, let's start with SQL code
This SQL code would give me the results I would like to have:
Select
ttsls.supp_inv_id,
isnull(ttsls.family_id,ima
ttsls.amount,
imaging.image_name
from
ttsls full outer join imaging on imaging.family_id = ttsls.family_id
Data:
supp_inv_id family_id family_id amount image_name
----------- ----------- ----------- ----------- --------------------------
1 11 11 10 image1
2 12 12 15 image2
3 13 13 5 image3
4 NULL NULL 7 NULL
5 NULL NULL 9 NULL
NULL 16 16 NULL image3
NULL 17 17 NULL image4
How would I get the same dataset using LINQ, let's say I have these two table in DataTable objects
How would I get this Outer join to work?
Thanks in advance
Base from what I am reading about LINQ Full outher join is not supported, and I could not really find a good example of a workaround for outer join in LINQ to Dataset.
Here is an example I would like to see working with LINQ to Dataset
Let's start with SQL server side with two tables
Table 1
CREATE TABLE [dbo].[ttsls](
[supp_inv_id] [int] NULL,
[family_id] [int] NULL,
[amount] [int] NULL
) ON [PRIMARY]
Table 2
CREATE TABLE [dbo].[imaging](
[family_id] [int] NOT NULL,
[image_name] [varchar](50) NULL
) ON [PRIMARY]
Here is the data in both tables
Table 1:
supp_inv_id family_id amount
----------- --------------------------
1 11 10
2 12 5
3 13 5
4 NULL 7
5 NULL 9
Table 2:
family_id image_name
----------- --------------------------
11 image1
12 image2
13 image3
16 image3
17 image4
Now I want to make a Full Join between these two tables with LINQ, let's start with SQL code
This SQL code would give me the results I would like to have:
Select
ttsls.supp_inv_id,
isnull(ttsls.family_id,ima
ttsls.amount,
imaging.image_name
from
ttsls full outer join imaging on imaging.family_id = ttsls.family_id
Data:
supp_inv_id family_id family_id amount image_name
----------- ----------- ----------- ----------- --------------------------
1 11 11 10 image1
2 12 12 15 image2
3 13 13 5 image3
4 NULL NULL 7 NULL
5 NULL NULL 9 NULL
NULL 16 16 NULL image3
NULL 17 17 NULL image4
How would I get the same dataset using LINQ, let's say I have these two table in DataTable objects
How would I get this Outer join to work?
Thanks in advance
Last Comment
ASKER
Would be nice to see the same article in C# Linq seem to be very diffrent in VB.net
I'm going to converted the code above to C# :D just wait for a while
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
.NET Programming
The .NET Framework is not specific to any one programming language; rather, it includes a library of functions that allows developers to rapidly build applications. Several supported languages include C#, VB.NET, C++ or ASP.NET.
137K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Here is the VB Code, just conver to C# code for your self:
http://blogs.msdn.com/vbteam/archive/2008/02/12/converting-sql-to-linq-part-9-full-outer-join-bill-horst.aspx