I'm having some problems writing a query that returns results where there is a relation and where theres no relation.
Let's take a look at this simple piece of code:
int customerID = 1;
var ctxApps = from ctxApp in db.AppNames
where ctxApp.Application.CustomerID != customerID
orderby ctxApp.Name ascending
select ctxApp;
This query returns all ctxApp where ctxApp.Application.CustomerID != 1. So far so good BUT it only returns the results where the association ctxApp.Application exists.
So my question is, how can I ALSO return the results where theres no association?
If I understand you right, you have a table AppNames and a table Applications. You would like to return all AppNames, regardless of whether the AppNames also has a related record in Applications. That is, you want the equivalent of a left join. The below should
accomplish that. But it's untested.
int customerID = 1;
var ctxApps = from ctxApp in db.AppNames
join a in db.Applications on a.PrimaryKey equals ctxApp.ForeignKey into j
from apps in j.DefaultIfEmpty()
where apps.CustomerID != customerID
orderby apps.Name ascending
select ctxApp;
If that's not what you're after, please post back.
Thanks for you answer. Seems like you understand me right, but it still doesn't work.
I've edited your example a little, and heres what I've tryed:
var ctxApps = from ctxApp in db.AppNames
join kmdApp in db.Applications on ctxApp.ID equals kmdApp.AppNameID into j
from apps in j.DefaultIfEmpty()
where apps.CustomerID != customerID
orderby ctxApp.Name ascending
select ctxApp;
It's returning the rows in AppNames where theres a relation to the Application table.
I apologize for the non-working query. I created a test app, and I think the following will produce the result you desire:
int customerID = 1;
dbmlDataContext db = new dbmlDataContext();
var ctxApps = from ctxApp in db.AppNames
join kmdApp in db.Applications on ctxApp.ID equals kmdApp.AppNameID into j
from apps in j.DefaultIfEmpty()
where ctxApp.Applications.Count == 0 || apps.ID != customerID
orderby ctxApp.AppName1 ascending
select ctxApp;
foreach(AppName n in ctxApps)
{
System.Diagnostics.Debug.WriteLine(
"AppName: " + n.AppName1 + "; related apps: " + n.Applications.Count);
}
I created two tables as follows:
CREATE DATABASE tstdb;
go
use tstdb;
CREATE TABLE AppNames(ID int PRIMARY KEY, AppName varchar(50));
INSERT INTO AppNames
SELECT 1, "Excel"
UNION
SELECT 2, "Word"
UNION
SELECT 3, "Access"
UNION
SELECT 4, "ShadyApplication"
go
CREATE TABLE Applications(ID int PRIMARY KEY, AppNameID int REFERENCES AppNames(ID))
go
INSERT INTO Applications
SELECT 1, 1
UNION
SELECT 2, 1
UNION
SELECT 3, 2
UNION
SELECT 4, 3
go
If I run the Linq query without the where clause, I will get:
AppName: Access; related apps: 1
AppName: Excel; related apps: 2
AppName: Excel; related apps: 2
AppName: ShadyApplication; related apps: 0
AppName: Word; related apps: 1
With the where clause:
AppName: Access; related apps: 1
AppName: Excel; related apps: 2
AppName: ShadyApplication; related apps: 0
AppName: Word; related apps: 1
Since it's a 1:1 relationship between two tables, you can use "Applications.(fieldName)" to access the child/parent fields. While if it's a 1:M relationship, you can use foreach to iterate "Applications". Please look at the following sample snippet. Hope
it helps you!
// For 1:1 relationship
var dd = from u
in tagTx.Tags
select new { u.TagID, u.Text, u.ArticlesTags.ArticleID };
// For 1:M relationship - Tag table is parent and ArticlesTag is child
foreach (Tag tag
in tagTx.Tags)
{
Response.Write(tag.TagID.ToString());
foreach (ArticlesTag at
in tag.ArticlesTags)
{
Response.Write(at.ArticleID.ToString());
}
}
Thanks.
Please mark the replies as answers if they help or unmark if not.
If you have any feedback about my replies, please contact msdnmg@microsoft.com
Not sure if this helps.. But heres some code from ASP.NET Unleashed that seems similar to what you are trying to do
var query = from c in db.MovieCategories
join m in db.Movies
on c.Id equals m.CategoryId into cm
from m in cm.DefaultIfEmpty()
select new {
c.Id, c.Name, m.Title
}
TJakobsen
Member
96 Points
52 Posts
LINQ to SQL. How to make this query?
Apr 25, 2008 07:51 AM|LINK
Hello.
I'm having some problems writing a query that returns results where there is a relation and where theres no relation.
Let's take a look at this simple piece of code:
int customerID = 1; var ctxApps = from ctxApp in db.AppNames where ctxApp.Application.CustomerID != customerID orderby ctxApp.Name ascending select ctxApp;This query returns all ctxApp where ctxApp.Application.CustomerID != 1. So far so good BUT it only returns the results where the association ctxApp.Application exists.
So my question is, how can I ALSO return the results where theres no association?
LINQ ORM Association
wisccal
Participant
762 Points
131 Posts
Re: LINQ to SQL. How to make this query?
Apr 25, 2008 08:26 PM|LINK
int customerID = 1;
var ctxApps = from ctxApp in db.AppNames
join a in db.Applications on a.PrimaryKey equals ctxApp.ForeignKey into j
from apps in j.DefaultIfEmpty()
where apps.CustomerID != customerID
orderby apps.Name ascending
select ctxApp;
If that's not what you're after, please post back.
===========
Regards,
Steve
www.stkomp.com
TJakobsen
Member
96 Points
52 Posts
Re: LINQ to SQL. How to make this query?
Apr 26, 2008 10:18 AM|LINK
Thanks for you answer. Seems like you understand me right, but it still doesn't work.
I've edited your example a little, and heres what I've tryed:
var ctxApps = from ctxApp in db.AppNames join kmdApp in db.Applications on ctxApp.ID equals kmdApp.AppNameID into j from apps in j.DefaultIfEmpty() where apps.CustomerID != customerID orderby ctxApp.Name ascending select ctxApp;It's returning the rows in AppNames where theres a relation to the Application table.
Any ideas?
wisccal
Participant
762 Points
131 Posts
Re: LINQ to SQL. How to make this query?
Apr 28, 2008 05:26 AM|LINK
Hi,
I apologize for the non-working query. I created a test app, and I think the following will produce the result you desire:
int customerID = 1;dbmlDataContext db = new dbmlDataContext();
var ctxApps = from ctxApp in db.AppNames
join kmdApp in db.Applications on ctxApp.ID equals kmdApp.AppNameID into j
from apps in j.DefaultIfEmpty()
where ctxApp.Applications.Count == 0 || apps.ID != customerID
orderby ctxApp.AppName1 ascending
select ctxApp;
foreach(AppName n in ctxApps)
{
System.Diagnostics.Debug.WriteLine(
"AppName: " + n.AppName1 + "; related apps: " + n.Applications.Count);
}
I created two tables as follows:
CREATE DATABASE tstdb;
go
use tstdb;
CREATE TABLE AppNames(ID int PRIMARY KEY, AppName varchar(50));
INSERT INTO AppNames
SELECT 1, "Excel"
UNION
SELECT 2, "Word"
UNION
SELECT 3, "Access"
UNION
SELECT 4, "ShadyApplication"
go
CREATE TABLE Applications(ID int PRIMARY KEY, AppNameID int REFERENCES AppNames(ID))
go
INSERT INTO Applications
SELECT 1, 1
UNION
SELECT 2, 1
UNION
SELECT 3, 2
UNION
SELECT 4, 3
go
If I run the Linq query without the where clause, I will get:
AppName: Access; related apps: 1AppName: Excel; related apps: 2
AppName: Excel; related apps: 2
AppName: ShadyApplication; related apps: 0
AppName: Word; related apps: 1
With the where clause:
AppName: Access; related apps: 1
AppName: Excel; related apps: 2
AppName: ShadyApplication; related apps: 0
AppName: Word; related apps: 1
Is that the behavior you were after?
=============
Regards,
Steve
www.stkomp.com
TJakobsen
Member
96 Points
52 Posts
Re: LINQ to SQL. How to make this query?
Apr 28, 2008 07:15 AM|LINK
Thank you once again! I really appreciate it.
How ever, we're not quite there yet :-)
In your query, the where clause should be: where apps.CustomerID != customerID
And because it's a one-to-one relation between the two tables (sorry I didn't tell you), I can't do: where ctxApp.Applications.Count == 0
How can we solve that?
Wencui Qian ...
All-Star
56784 Points
5796 Posts
Microsoft
Re: LINQ to SQL. How to make this query?
Apr 28, 2008 10:07 AM|LINK
Hi TJakobsen,
Since it's a 1:1 relationship between two tables, you can use "Applications.(fieldName)" to access the child/parent fields. While if it's a 1:M relationship, you can use foreach to iterate "Applications". Please look at the following sample snippet. Hope it helps you!
// For 1:1 relationship
var dd = from u in tagTx.Tags select new { u.TagID, u.Text, u.ArticlesTags.ArticleID };// For 1:M relationship - Tag table is parent and ArticlesTag is child
foreach (Tag tag in tagTx.Tags){
Response.Write(tag.TagID.ToString());
foreach (ArticlesTag at in tag.ArticlesTags){
Response.Write(at.ArticleID.ToString());
}
}
Thanks.
If you have any feedback about my replies, please contact msdnmg@microsoft.com
Microsoft One Code Framework
TJakobsen
Member
96 Points
52 Posts
Re: LINQ to SQL. How to make this query?
Apr 28, 2008 10:18 AM|LINK
Hi Wencui, thanks for your reply.
I know that, but I can't see how that helps me with my issue? Could you explain please?
dotnet_lee
Member
467 Points
207 Posts
Re: LINQ to SQL. How to make this query?
Apr 28, 2008 01:06 PM|LINK
Not sure if this helps.. But heres some code from ASP.NET Unleashed that seems similar to what you are trying to do
var query = from c in db.MovieCategories join m in db.Movies on c.Id equals m.CategoryId into cm from m in cm.DefaultIfEmpty() select new { c.Id, c.Name, m.Title }TJakobsen
Member
96 Points
52 Posts
Re: LINQ to SQL. How to make this query?
Apr 28, 2008 01:21 PM|LINK
Thanks for your reply dotnet_lee, but thats not any different from what we've tryed already.
Wencui Qian ...
All-Star
56784 Points
5796 Posts
Microsoft
Re: LINQ to SQL. How to make this query?
Apr 29, 2008 02:08 AM|LINK
Hi TJakobsen,
Of course, I can give you a sample for dealing with no relations. You need to modify the codes to suit your requirements.
When you deal with tables without any relationship, we can use join to achieve that.
For example, we use inner join to query the tables.
//Inner join
sel = from u in tagTx.Tags from p in tagTx.ArticlesTags where u.TagID == p.TagID select new{
u.TagID,
u.Text,
p.ArticleID
};
Or we can also use left join as above.
//Left join
var sel = from u in tagTx.Tags join p in tagTx.ArticlesTags on u.TagID equals p.TagID into UP from p in UP.DefaultIfEmpty() select new{
u.TagID,
u.Text,
p.ArticleID
};
Thanks.
If you have any feedback about my replies, please contact msdnmg@microsoft.com
Microsoft One Code Framework