I run into a few issues while testing Dlinq out. Here is a simple program that demonstrates them. Not sure if all of them are real bugs as some could be caused by my mistakes.
using System;
using
System.Collections.Generic;using
System.Text;using
System.Query;using
System.Xml.XLinq;using
System.Data.DLinq;using
Microsoft.VisualStudio.TestTools.UnitTesting;namespace
LINQConsoleApplication{
class Program{
static void Main (string[] args){
DlinqDemo db = new DlinqDemo(@"Data Source=localhost\sqlexpress;Initial Catalog=DlinqDemo;Integrated Security=True"); //create some seed data for the demo Hospital hos = new Hospital(){Name = "FastServiceHospital"}; Doctor doc = new Doctor(){Name = "Mr. Smith", Hospital = hos}; Patient pat = new Patient () { Name = "Mr. Sick", Doctor = doc, Age = 50, Enrolldate = DateTime.UtcNow };db.Patients.Add(pat);
db.SubmitChanges();
/***********************************************/ /* issue with RejectChanges() */ // the case that works... Hospital hos1 = new Hospital(){Name = "FastServiceHospital"};db.Hospitals.Add(hos1);
try{db.SubmitChanges();
}
catch(Exception e){
//Exception due to primary key constraintdb.RejectChanges();
}
//succeeddb.SubmitChanges();
//the case that fails... Patient pat1 = new Patient(){Name = "Mr. Sick", Doctor = doc, Age = 50, Enrolldate = DateTime.UtcNow}; try{db.SubmitChanges();
}
catch(Exception e){
//Exception due to primary key constraintdb.RejectChanges();
}
//Exception is thrown again because of the violation of primary key. it seems that RejectChanges() was unable to role back all the changes //when an entity was not added through the table but through attachment to another entity that the data context is aware of.db.SubmitChanges();
/**************************************************/ /* issue with foreign key of type nvarchar */ //the case that works... Patient pat2 = new Patient(){Name = "Mr. Brown", DoctorFK = doc.Id, Age = 50, Enrolldate = DateTime.UtcNow};db.Patients.Add(pat2);
db.SubmitChanges();
//the case that fails... Doctor doc3 = new Doctor(){Name = "Mr. Jones", HospitalFK = hos.Name};db.Doctors.Add(doc3);
//Exception: Cannot insert the value NULL into column 'hospitalFK', table 'DlinqDemo.dbo.doctor'; column does not allow nulls. INSERT fails.\r\nThe statement has been terminateddb.SubmitChanges();
/***************************************************/ /* issue with the Including() */ // the following line fails to compile: Error 1 The type arguments for method 'System.Data.DLinq.QueryExtensions.Including<T>(System.Data.DLinq.Query<T>, params System.Expressions.Expression<System.Query.Func<T,object>>[])' cannot be inferred from the usage. Try specifying the type arguments explicitly. D:\My Documents\Visual Studio 2005\Projects\LINQConsoleApplication1\LINQConsoleApplication1\Program.cs 53 44 LINQConsoleApplication1 //var p = (from i in db.Patients select i).Including(i => i.Doctor.Including(j => j.Hospital)); /****************************************************/ /* issue with the Where operator */ //the case that works... var p = from i in db.Patients where i.Enrolldate < (DateTime.UtcNow + TimeSpan.FromDays(1)) select i;p.ToList();
//the case that fails... Runtime exception: Invalid cast from 'System.DateTime' to 'System.TimeSpan' //i've also run into some other types of runtime exception associated with datetime/timespan related calculation in a where clause. var p1 = from i in db.Patients where (i.Enrolldate - TimeSpan.FromDays(1)) < DateTime.UtcNow select i;p1.ToList();
/***************************************************/ /* issue with data context caching... */ DlinqDemo db1 = new DlinqDemo(@"Data Source=localhost\sqlexpress;Initial Catalog=DlinqDemo;Integrated Security=True"); Patient pat3 = db1.Patients.First(i => i.Name == "Mr. Sick"); int age = pat.Age; //modify the age through another data context Patient pat4 = db.Patients.First(i => i.Name == "Mr. Sick");pat4.Age += 1;
db.SubmitChanges();
//Not query is issued to the db... this is in memory Patient pat5 = db1.Patients.First(i => i.Name == "Mr. Sick"); //assertion fails Assert.IsTrue(age == pat5.Age); //Query issued to the db... var p2 = from i in db1.Patients where (i.Name == "Mr. Sick") select new { Age = i.Age}; //assertion succeedsAssert.IsTrue(age == p2.First().Age);
}
}
}
/*script used to setup the db*/
SET
ANSI_NULLS ONGO
SET
QUOTED_IDENTIFIER ONGO
IF
NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[hospital]') AND type in (N'U'))BEGIN
CREATE
TABLE [dbo].[hospital]([name] [nvarchar]
(50) NOT NULL, CONSTRAINT [PK_hospital] PRIMARY KEY CLUSTERED(
[name]
ASC)
WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY])
ON [PRIMARY]END
GO
SET
ANSI_NULLS ONGO
SET
QUOTED_IDENTIFIER ONGO
IF
NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[doctor]') AND type in (N'U'))BEGIN
CREATE
TABLE [dbo].[doctor]([name] [nvarchar]
(50) NOT NULL,[id] [int]
IDENTITY(1,1) NOT NULL,[hospitalFK] [nvarchar]
(50) NOT NULL, CONSTRAINT [PK_doctor] PRIMARY KEY CLUSTERED(
[id]
ASC)
WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY])
ON [PRIMARY]END
GO
SET
ANSI_NULLS ONGO
SET
QUOTED_IDENTIFIER ONGO
IF
NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[patient]') AND type in (N'U'))BEGIN
CREATE
TABLE [dbo].[patient]([name] [nvarchar]
(50) NOT NULL,[doctorFK] [int]
NOT NULL,[enrolldate] [datetime]
NOT NULL,[age] [int]
NOT NULL, CONSTRAINT [PK_patient] PRIMARY KEY CLUSTERED(
[name]
ASC)
WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY])
ON [PRIMARY]END
GO
IF
NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_doctor_hospital]') AND parent_object_id = OBJECT_ID(N'[dbo].[doctor]'))ALTER
TABLE [dbo].[doctor] WITH CHECK ADD CONSTRAINT [FK_doctor_hospital] FOREIGN KEY([hospitalFK])REFERENCES
[dbo].[hospital] ([name])GO
IF
NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_patient_doctor]') AND parent_object_id = OBJECT_ID(N'[dbo].[patient]'))ALTER
TABLE [dbo].[patient] WITH CHECK ADD CONSTRAINT [FK_patient_doctor] FOREIGN KEY([doctorFK])REFERENCES
[dbo].[doctor] ([id])
A few Dlinq bugs
jessj
I agree with you on both comments related to foreign key and caching. There are debate over this two areas and it is why I would say it is the "current" design behaviour.
By the way,
The right behavior is to always return the cached data and provide a way to explicitly invalidate the cache.
For your example, it cached the "patient" in the first query. In the second query, it is querying the "age" of the first patient.
I understand we could find out the age of the patient from the paitent object we cached. But as I know, we are currently doing caching on entity level only.
The right behavior is to always return the cached data and provide a way to explicitly invalidate the cache.
Currently, you could invalidate the cache by doing something like "db = new DlinqDemo("XXX")"
Atilla
For foreign key: Please don't fix the bug in the wrong way :) I think the desired behavior is that the foreign key field shoud take precedence when the reference has not been set.
For data caching: hmm... it doesn't make sense. the behavior is in consistant. db1 should have the value of Age of "Mr. Sick" after the query Patient pat3 = db1.Patients.First(i => i.Name == "Mr. Sick"). If cache is always used, new query is not necessary for var p2 = from i in db1.Patients where (i.Name == "Mr. Sick") select new { Age = i.Age}; The right behavior is to always return the cached data and provide a way to explicitly invalidate the cache.
gurminder
Thanks for the reply. Just two follow up questions:
1. Foreign Key Issue - The problem was with nvarchar and not of foreign in general. The answer provided in http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=228964&SiteID=1 fails to provide an explanation. In claims that “if the reference does not match the foriegn-key values will overwrite your currently foreign-key values so that they match the reference. “ However, in the example that I provided:
Patient pat2 = new Patient(){Name = "Mr. Brown", DoctorFK = doc.Id, Age = 50, Enrolldate = DateTime.UtcNow};
db.Patients.Add(pat2);
db.SubmitChanges();
pat.Doctor is indeed null, but the record was successfully inserted into the DB. If you look at the query generated, Dlinq correctly specified doc.Id for the corresponding column in the insert statement. However if the foreign key is of nvarchar, Dlinq uses null in the insert statement.
2. Data caching - could you elaborate on why
Query is NOT issued for: Patient pat5 = db1.Patients.First(i => i.Name == "Mr. Sick");
Query is issued for: var p2 = from i in db1.Patients where (i.Name == "Mr. Sick") select new { Age = i.Age};
How does Dlinq decide when and when not to use the cached data
TaTas
You are right, I am over look on the foreign key issue, I will log the issue.
For the data caching problem, here is the explaination.
Patient pat3 = db1.Patients.First(i => i.Name == "Mr. Sick");// Patient in db1 data context
Patient pat4 = db.Patients.First(i => i.Name == "Mr. Sick");pat4.Age += 1;
db.SubmitChanges();
// Changing patient in db data context
Patient pat5 = db1.Patients.First(i => i.Name == "Mr. Sick");// Query the first patient again from db1 data context and there is already one in there. No database query is needed. -- It will return the cached patient in db1 data context to you.
var p2 = from i in db1.Patients where (i.Name == "Mr. Sick") select new { Age = i.Age};// Query the age of the first patient from db1 data context, the "patient" is cached but the "Age of the patient" is not. Query the database again to get the current data for you.
//Note: For a patient, we could use the primary key to identify the object are the same, but for "Age", it is just an integer value.
I hope this help
Ares01
Thank you for your feedback.
Here are the quick answer for the issues you find.
var custs = (
from c in db.Customers
where c.City == "Sao Paulo"
select c )
.Including(c => c.Orders.Including(d => d.OrderDetails));