It has been created with the mssql enterprise manager, and the relation has been added as follows:
GO
ALTER TABLE [dbo].[KMD_Apps] WITH NOCHECK ADD CONSTRAINT [FK_KMD_Apps_LU_APPNAME] FOREIGN KEY([FK_APPNAMEID])
REFERENCES [dbo].[LU_APPNAME] ([PK_APPNAMEID])
As you can see from my earlier post, I also defined that FK constraint on the Applications table
CREATE TABLE Applications(ID int PRIMARY KEY, AppNameID int REFERENCES AppNames(ID), CustomerID int, ContractID int)
So, I really don't know why your LINQ DBML file seems to be different from mine.
One last thing I can think of is, instead of "where ctxApp.Application == null", try "where ctxApp.Application.ID == null".
If you you select records from the AppNames table that do not have a record in Applications, what values do their Application objects have? In the example I provided:
var ctxApps = from ctxApp in db.AppNames
where ctxApp.Name = "ShadyApplication"
select ctxApp;
foreach(AppName n in ctxApps)
{
System.Diagnostics.Debug.WriteLine(
string.Format("Name: {0}, ApplicationID: {1}", n.Name, n.Application.ID);
}
Heres a few applications that doesn't have a relation to the Application table:
From the AppName table:
Name: kmdpxgp6_uvm2035, AppID: 1639
Name: kmdpxgp7_uvm2031, AppID: 1640
Name: kmdpxgp8_uvm2007, AppID: 1641
Does that help you? You know, that they will always exist in AppName table. If they exist in the Application table, it's because they are bound to a customer. And here we're trying to select all the applications thats not bound to a given customer. AppNames
thats bound to other customers than the given one should still be listed.
When I do the following on an application thats not bound to a customer in the Application table, I get an error:
var test = from ctxApp in db.AppNames
select ctxApp;
foreach (AppName n in test)
{
System.Diagnostics.Debug.WriteLine(
string.Format("AppName_Name: {0}, AppName_ID: {1}\n Application_ID: {2}, Application_CustomerID: {3}",
n.Name, n.ID, n.Application.ID, n.Application.CustomerID));
}
Error:
A first chance exception of type 'System.NullReferenceException'
occurred in ASP.Website.M1125.DLL
I'm running out of ideas here. Your LINQ model seems to be different from the one that was generated on my machine. I can't think of any other possible solutions. Unless you post your dbml file and CREATE TABLE statements for the Applications & AppNames
tables, I don't believe I'm able to help.
The only way I could make your DataContext class work is by removing the IsForeignKey attribute on the AppName.Application Property.
That is, instead of ...
[Association(Name="Application_AppName", Storage="_Application", ThisKey="ID", OtherKey="AppNameID",
IsForeignKey=true)]
public Application Application
{
...
}
...try this...
[Association(Name="Application_AppName", Storage="_Application", ThisKey="ID", OtherKey="AppNameID")]
public Application Application
{
...
}
"ctxApps.Application == null" then gave the desired results.
I hope this helps, and you're able to make this modification. Otherwise, I can't give you any more advise. Maybe, you should also try re-generating your DataContext, because that attribute really doesn't seem to belong there. In my file, IsForeignKey is
only defined on the Application.AppNames property. In your file, both Application.AppNames and AppName.Application have this, which doesn't seem right. According to MSDN, this "gets whether the other type is the parent of this type". So, AppNames is parent
of Applications, so I guess it should only be set on Application.AppNames.
By the way, when I created the tables with your SQL statements and then dragged them onto the LINQ2SQL designer in VS, it generated the classes correctly. The query I provided earlier worked perfectly.
wisccal
Participant
762 Points
131 Posts
Re: LINQ to SQL. How to make this query?
May 06, 2008 05:38 AM|LINK
Hi,
Sorry about being dense, but how did you define that one-to-one relationship? I added a unique constraint as follows:
ALTER TABLE applications ADD CONSTRAINT UQ_Applications_AppNameID UNIQUE (AppNameID)
But it still populates an AppNames object with an Applications list, and therefore, it allows me to use Count.
=============
Regards,
Steve
www.stkomp.com
TJakobsen
Member
96 Points
52 Posts
Re: LINQ to SQL. How to make this query?
May 06, 2008 01:03 PM|LINK
It has been created with the mssql enterprise manager, and the relation has been added as follows:
Does this help you?
wisccal
Participant
762 Points
131 Posts
Re: LINQ to SQL. How to make this query?
May 07, 2008 05:19 AM|LINK
Hi,
As you can see from my earlier post, I also defined that FK constraint on the Applications table
CREATE TABLE Applications(ID int PRIMARY KEY, AppNameID int REFERENCES AppNames(ID), CustomerID int, ContractID int)
So, I really don't know why your LINQ DBML file seems to be different from mine.
One last thing I can think of is, instead of "where ctxApp.Application == null", try "where ctxApp.Application.ID == null".
If you you select records from the AppNames table that do not have a record in Applications, what values do their Application objects have? In the example I provided:
var ctxApps = from ctxApp in db.AppNames
where ctxApp.Name = "ShadyApplication"
select ctxApp;
foreach(AppName n in ctxApps)
{
System.Diagnostics.Debug.WriteLine(
string.Format("Name: {0}, ApplicationID: {1}", n.Name, n.Application.ID);
}
What would that give you?
================
Regards,
Steve
www.stkomp.com
TJakobsen
Member
96 Points
52 Posts
Re: LINQ to SQL. How to make this query?
May 07, 2008 07:25 AM|LINK
Hi,
"where ctxApp.Application.ID == null" doesn't work.
Heres a few applications that doesn't have a relation to the Application table:
From the AppName table:
Name: kmdpxgp6_uvm2035, AppID: 1639
Name: kmdpxgp7_uvm2031, AppID: 1640
Name: kmdpxgp8_uvm2007, AppID: 1641
Does that help you? You know, that they will always exist in AppName table. If they exist in the Application table, it's because they are bound to a customer. And here we're trying to select all the applications thats not bound to a given customer. AppNames thats bound to other customers than the given one should still be listed.
When I do the following on an application thats not bound to a customer in the Application table, I get an error:
var test = from ctxApp in db.AppNames select ctxApp; foreach (AppName n in test) { System.Diagnostics.Debug.WriteLine( string.Format("AppName_Name: {0}, AppName_ID: {1}\n Application_ID: {2}, Application_CustomerID: {3}", n.Name, n.ID, n.Application.ID, n.Application.CustomerID)); } Error: A first chance exception of type 'System.NullReferenceException' occurred in ASP.Website.M1125.DLLCan we use that for something? :-)
wisccal
Participant
762 Points
131 Posts
Re: LINQ to SQL. How to make this query?
May 07, 2008 02:52 PM|LINK
Hi,
I'm running out of ideas here. Your LINQ model seems to be different from the one that was generated on my machine. I can't think of any other possible solutions. Unless you post your dbml file and CREATE TABLE statements for the Applications & AppNames tables, I don't believe I'm able to help.
Or maybe someone else can give their insight?
==============
Regards,
Steve
www.stkomp.com
TJakobsen
Member
96 Points
52 Posts
Re: LINQ to SQL. How to make this query?
May 07, 2008 03:31 PM|LINK
This is so strange.
I've posted them over at pastebin.com:
DBML file: http://pastebin.com/f62ce60bf
Application table (dbo.Apps): http://pastebin.com/f485c00ee
AppName table (dbo.LU_APPNAME): http://pastebin.com/f1644ad8b
I hope this helps. Thanks in advance wisccal.
wisccal
Participant
762 Points
131 Posts
Re: LINQ to SQL. How to make this query?
May 08, 2008 06:32 AM|LINK
Hey,
The only way I could make your DataContext class work is by removing the IsForeignKey attribute on the AppName.Application Property.
That is, instead of ...
[Association(Name="Application_AppName", Storage="_Application", ThisKey="ID", OtherKey="AppNameID", IsForeignKey=true)]
public Application Application
{
...
}
...try this...
[Association(Name="Application_AppName", Storage="_Application", ThisKey="ID", OtherKey="AppNameID")]
public Application Application
{
...
}
"ctxApps.Application == null" then gave the desired results.
I hope this helps, and you're able to make this modification. Otherwise, I can't give you any more advise. Maybe, you should also try re-generating your DataContext, because that attribute really doesn't seem to belong there. In my file, IsForeignKey is only defined on the Application.AppNames property. In your file, both Application.AppNames and AppName.Application have this, which doesn't seem right. According to MSDN, this "gets whether the other type is the parent of this type". So, AppNames is parent of Applications, so I guess it should only be set on Application.AppNames.
By the way, when I created the tables with your SQL statements and then dragged them onto the LINQ2SQL designer in VS, it generated the classes correctly. The query I provided earlier worked perfectly.
==============
Regards,
Steve
www.stkomp.com