Linq newbie FK ref question

Hi,
I've started using Linq rather than Nhibernate in some tests/personal apps I'm using. Linq is fantastic and far more natural to use than Nhib, with as much power. c# 3 features allows us to fully utilise these persistable graphs to the most, with interesting set-based processing capabilities.

However, in my Unit Tests I retrieve a particular object graph (Customer -> Client) whose FK relation (Customer.ClientID -> Client) is non-null. If I add several of these objects to a parent collection (Customer Groups [] -> Customer -> Client) and persist (db.CustomerGroups.Add) I get unusual behaviour.

If I run the test on it's own it passes ->
SELECT TOP 1 [t0].[ClientID], [t0].[ClientName], [t0].[AddressPostID] FROM [Client] AS [t0]
Start Local Transaction (ReadCommitted)
INSERT INTO [Contact](Firstname, Lastname, Role, ClientID, PayRateID, Notes) VALUES(@p0, @p1, @p2, @p3, @p4, @p5)
SELECT [t0].[ContactID] FROM [Contact] AS [t0] WHERE [t0].[ContactID] = (CONVERT(Int,@@IDENTITY))

Commit Local Transaction

And the deletes run too .. ->

Start Local Transaction (ReadCommitted)
DELETE FROM [Contact] WHERE [ContactID] = @p0

Commit Local Transaction
SELECT COUNT(*) AS [value] FROM [Contact] AS [t0] WHERE [t0].[ContactID] = @p0

Now. If I run the whole suite of tests, The first passes but the rest fail, with a duplicate insert AND NULL column failure:
SELECT TOP 1 [t0].[ClientID], [t0].[ClientName], [t0].[AddressPostID] FROM [Client] AS [t0]

Start Local Transaction (ReadCommitted)
INSERT INTO [Contact](Firstname, Lastname, Role, ClientID, PayRateID, Notes) VALUES(@p0, @p1, @p2, @p3, @p4, @p5)
SELECT [t0].[ContactID] FROM [Contact] AS [t0] WHERE [t0].[ContactID] = (CONVERT(Int,@@IDENTITY))

INSERT INTO [Contact](Firstname, Lastname, Role, ClientID, PayRateID, Notes) VALUES(@p0, @p1, @p2, @p3, @p4, @p5)
SELECT [t0].[ContactID] FROM [Contact] AS [t0] WHERE [t0].[ContactID] = (CONVERT(Int,@@IDENTITY))

Rollback Local Transaction

System.Data.SqlClient.SqlException: Cannot insert the value NULL into column 'ClientID', table 'quant-cont-normal.dbo.Contact'; column does not allow nulls. INSERT fails.
The statement has been terminated.

My other tests are a variation on the above (adding via collection or List or ID) as well as persisting other kinda of objects. Order of the tests dont seems to affect it.

Any ideas where I'm going wrong

<Table Name="Contact">
<Type Name="Leno.Domain.Contact">
<Column Name="ContactID" Member="ContactID" Storage="_ContactID" DbType="Int NOT NULL IDENTITY" IsIdentity="True" IsAutoGen="True" />
<Column Name="Firstname" Member="Firstname" Storage="_Firstname" DbType="VarChar(255)" />
<Column Name="Lastname" Member="Lastname" Storage="_Lastname" DbType="VarChar(255)" />
<Column Name="Role" Member="Role" Storage="_Role" DbType="VarChar(255)" />
<Column Name="ClientID" Member="ClientID" Storage="_ClientID" DbType="Int NOT NULL" />
<Association Name="FK_Contact_Client" Member="Client" Storage="_Client" ThisKey="ClientID" OtherTable="Client" OtherKey="ClientID" IsParent="True" />
</Type>
</Table>
<Table Name="Client">
<Type Name="Leno.Domain.Client">
<Column Name="ClientID" Member="ClientID" Storage="_ClientID" DbType="Int NOT NULL IDENTITY" IsIdentity="True" IsAutoGen="True" />
<Column Name="ClientName" Member="ClientName" Storage="_ClientName" DbType="NChar(10)" />
</Type>
</Table>

Thanks ian

Answer this question

Linq newbie FK ref question

OB
Use a new 'db' instance for each test. That should make the tests behave consistent with running them separately.
geetasain
Here's the test classes. Both pass in isolation, sencod fails if run together, with a null exception and an extra Contact insert.

[Test]
public void AddContactMin()
{
Client cl = (from clset in Db.Client
select clset).First();
if (null == cl) cl = new Client("cl1", null);
Contact c = new Contact(cl);
Db.Contact.Add(c);
Db.SubmitChanges();
Assert.AreEqual(1, (from set in Db.Contact
where set.ContactID == c.ContactID
select set).Count());
Db.Contact.Remove(c);
Db.SubmitChanges();
Assert.AreEqual(0, (from set in Db.Contact
where set.ContactID == c.ContactID
select set).Count());
}
[Test]
public void AddContactMin2()
{
Client cl = (from clset in Db.Client
select clset).First();
if (null == cl) cl = new Client("cl1", null);
Contact c = new Contact(cl);
Db.Contact.Add(c);
Db.SubmitChanges();
Assert.AreEqual(1, (from set in Db.Contact
where set.ContactID == c.ContactID
select set).Count());
Db.Contact.Remove(c);
Db.SubmitChanges();
Assert.AreEqual(0, (from set in Db.Contact
where set.ContactID == c.ContactID
select set).Count());
}
DanLi
Thanks for that MAtt. Feel like a plum.
Is there any performance hit from using a new DB isntance for each test Are there any issues with concurrency etc using many different DB instnaces

I've used NHibernate a little and know Factory creation is hugely expensive ...

Thanks again !
ian
Linq newbie FK ref question

Answer this question