In this post I will show few basic scenarios and related problems by daily using of the Linq2Sql. Before all take a look on next two pictures which represent the database and the data-context used in samples in this post.
Retrieving of some item
Following very trivial example shows how to retrieve the first item from TTransponder table.
DalContext dal1 = new DalContext();
TTransponder t = dal1.TTransponders.First(); |
And this is the SQL statement which is executedto retrieve the item.
SELECT TOP (1) [t0].[TransponderID], [t0].[Type], [t0].[Data], [t0].[Vendor], [t0].[UID],
[t0].[LastChange], [t0].[ChangedBy], [t0].[ItemID]
FROM [dbo].[TTransponder] AS [t0] |
Now, let’s repeat the same statement to see if the SQL statement is repeated or the item value is retrieved from some kind of the cache:
DalContext dal1 = new DalContext();
TTransponder t =
dal1.TTransponders.First();
t = dal1.TTransponders.First(); |
The next SQL statement behind the code above, shows that no caching is performed.
SELECT TOP (1) [t0].[TransponderID], [t0].[Type], [t0].[Data], [t0].[Vendor], [t0].[UID],
[t0].[LastChange], [t0].[ChangedBy], [t0].[ItemID]
FROM [dbo].[TTransponder] AS [t0]
SELECT TOP (1) [t0].[TransponderID], [t0].[Type], [t0].[Data], [t0].[Vendor], [t0].[UID],
[t0].[LastChange], [t0].[ChangedBy], [t0].[ItemID]
FROM [dbo].[TTransponder] AS [t0] |
Creating of the new item
Following example shows how to insert the object which is created from the scratch or received as the new one over the wire (i.e.: Web Service). Note that such items are not attached to context. That means they are not retrieved as a result of some Ling2Sql operation.
DalContext dal1 = new DalContext();
TItem item = new TItem();
item.ItemID = Guid.NewGuid();
item.ItemDefinitionID = new Guid("256D0888-2EA7-4DD0-A5AF-84F1DEBDAC5B");
dal1.TItems.InsertOnSubmit(item);
dal1.SubmitChanges(); |
This code block generates following SQL statement:
exec sp_executesql N'INSERT INTO [dbo].[TItem]
([ItemID], [UID], [ItemDefinitionID], [NextProcessID], [LastChange], [ChangedBy], [OrderNumber], [ItemPointCreated], [ItemPointChanged], [CurrentProcessID])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9)',N'@p0 uniqueidentifier,@p1 nvarchar(4000),@p2 uniqueidentifier,@p3 uniqueidentifier,@p4 datetime,@p5 nvarchar(4000),@p6 nvarchar(4000),@p7 uniqueidentifier,@p8 uniqueidentifier,@p9 uniqueidentifier',@p0='F1961142-1A23-4B8B-8E0F-840FE6FCA54A',@p1=NULL,@p2='256D0888-2EA7-4DD0-A5AF-84F1DEBDAC5B',@p3=NULL,@p4=NULL,@p5=NULL,@p6=NULL,@p7='00000000-0000-0000-0000-000000000000',@p8='00000000-0000-0000-0000-000000000000',@p9=NULL |
Updating of the attached item
Following example shows how to update an existing detached item. This examples looks very simple, but it hides many theoretical problems.
The most simple scenario is to load the item, to change some properties and to submit changes. In this case we talk about updating of an attached item. This is shown in the next example. This example shows loading and updating of TTransponder and TItem entities.
DalContext dal1 = new DalContext();
TTransponder t = dal1.TTransponders.First();
t.LastChange = DateTime.Now;
dal1.SubmitChanges();
var oldItems = from i in dal1.TItems
where i.ItemID == new Guid("11111111-EA9C-4A5D-8356-1A9D6E0B1889"
select i;
TItem oldItem = oldItems.First();
oldItem.LastChange = DateTime.Now;
dal1.SubmitChanges(); |
Following SQL statement corresponds to the update of oldItem instance only.
|
exec sp_executesql N'UPDATE [dbo].[TItem] SET [LastChange] = @p6
WHERE ([ItemID] = @p0) AND ([UID] = @p1) AND ([ItemDefinitionID] = @p2) AND ([NextProcessID] IS NULL) AND ([LastChange] = @p3) AND ([ChangedBy] IS NULL) AND ([OrderNumber] IS NULL) AND ([ItemPointCreated] = @p4) AND ([ItemPointChanged] = @p5) AND ([CurrentProcessID] IS NULL)',
N'@p0 uniqueidentifier,@p1 nvarchar(3),@p2 uniqueidentifier,@p3 datetime,@p4 uniqueidentifier,@p5 uniqueidentifier,@p6 datetime',@p0='11111111-EA9C-4A5D-8356-1A9D6E0B1889',@p1=N'111',@p2='256D0888-2EA7-4DD0-A5AF-84F1DEBDAC5B',@p3='2008-01-20 19:26:58:430',@p4='00000000-0000-0000-0000-000000000000',@p5='00000000-0000-0000-0000-000000000000',@p6='2008-01-20 19:27:18:580' |
Updating of the detached item
Now, assume there is an instance of the item which has not been created by loading from the database as shown in the previous example.
First we create the context and then create an instance of type TItem. Note that this instance has nothing to do with the context (detached). The values of properties are carefully chosen, because this instance should represent an item which already exists in the database. Take o look on SQL statement below and it will be clear.
After that we create almost the same instance of
TItem entity, but with UID property, which will be set to some new value. Note that the old one has a value “UID1” and the new one is set on “UID2”. Note also that all other values, which are not changed, HAVE to be same in both instances. After all, both instances have to be attached to the context, because they have been created from the scratch and not retrieved from database.
DalContext dal1 = new DalContext();
TItem oldItem = new TItem();
oldItem.ItemID = new Guid("11111111-EA9C-4A5D-8356-1A9D6E0B1889");
oldItem.ItemDefinitionID =
new Guid("256D0888-2EA7-4DD0-A5AF-84F1DEBDAC5B");
oldItem.UID = "UID1";
TItem item = new TItem();
item.ItemID =
new Guid("11111111-EA9C-4A5D-8356-1A9D6E0B1889");
item.ItemDefinitionID =
new Guid("256D0888-2EA7-4DD0-A5AF-84F1DEBDAC5B");
item.UID = "UID2";
dal1.TItems.Attach(item, oldItem);
dal1.SubmitChanges(); |
Attaching to context by using both values allow comparison of two instances. Previous code block generates following SQL statement:
exec sp_executesql N'UPDATE [dbo].[TItem]
SET [UID] = @p5
WHERE ([ItemID] = @p0) AND ([UID] = @p1) AND ([ItemDefinitionID] = @p2) AND ([NextProcessID] IS NULL)
AND ([LastChange] IS NULL) AND
([ChangedBy] IS NULL) AND ([OrderNumber] IS NULL) AND
([ItemPointCreated] = @p3) AND ([ItemPointChanged] = @p4) AND ([CurrentProcessID] IS NULL)',
N'@p0 uniqueidentifier,
@p1 nvarchar(4),
@p2 uniqueidentifier,
@p3 uniqueidentifier,
@p4 uniqueidentifier,
@p5 nvarchar(4)',
@p0='11111111-EA9C-4A5D-8356-1A9D6E0B1889',
@p1=N'UID1',
@p2='256D0888-2EA7-4DD0-A5AF-84F1DEBDAC5B',
@p3='00000000-0000-0000-0000-000000000000',
@p4='00000000-0000-0000-0000-000000000000',
@p5=N'UID2' |
The code above will be mostly used in cases when your item has not been created as a result of an Linq operation. For example, in case of ASP.NET applications, query and update are done for separate requests to the Web server. Hence, it is impractical to use the same DataContext instance across multiple requests. In such cases, a DataContext instance needs to be able to update objects that it has not retrieved. The multi-tier entity support in LINQ to SQL provides such a capability through the Attach(item, item) method. Similarly, the same procedure
you will use in a web service.
Another easier possibility to perform the update would be:
TItem item = new TItem();
item.ItemID =
new Guid("11111111-EA9C-4A5D-8356-1A9D6E0B1889");
item.ItemDefinitionID =
new Guid("256D0888-2EA7-4DD0-A5AF-84F1DEBDAC5B");
item.UID = "UID2";
dal1.TItems.Attach(item, true);
dal1.SubmitChanges();
|
Unfortunately, when this is executed, following error appears:
“An entity can only be attached as modified without original state if it declares a version member or does not have an update check policy.”
This means, that you need a timestamp column in the table and corresponding property in the model. At this point I have to say, that in my >20 years of software development timestamp was used very rarely, for good reasons.
At the end there is one more thing I didn't described. Sometimes there will be a case to load the item in one context instance and to update it in the another one. This scenario is not supported at all, but sometimes very important. For more information about this and detaching of entities see this post.
Posted
Jan 20 2008, 07:46 PM
by
Damir Dobric