Linq to sql – мощный и эффективный инструмент для работы с БД. Но у него, как и у любого инструмента, существуют свои особенности, знание которых поможет сберечь драгоценное время и нервы. Статья написана в виде описания проблемы и ее решения в порядке увеличения времени, которое было затрачено на поиск этого решения. Итак, приступим.
Исходные данные
Пусть, у нас есть некая тестовая табличка, в которой хранится информация о выполненных пользователем действиях:
Где UserID – соответственно, идентификатор пользователя, DateCollected – дата сбора статистики, Type – может быть все что угодно, например, тип оплаты или тип выполненной пользователем операции (например, 2 – почесать нос), а Value – результат выполнения операции (например, 1 – успешно, 0 – не успешно).
1. Почему данные не апдейтятся?
Предположим, что пользователь у нас работает с несколькими экземплярами приложения, или сидит на сайте одновременно с нескольких браузеров и выполняет какие-нибудь однотипные действия (например, с Type = 2). А нам необходимо хранить статистику только для последней операции для каждого типа. Пусть у нас есть такой простой код, который сохраняет статистику:
// результат операции
var value = 15;
// сохраняем результат в БД
using(var context = new DataBaseObjectsDataContext(connectionString)){
context.UserStatistics
.Where(item => item.Type == 2)
.First()
.Value = value;
context.SubmitChanges();
}
Но что произойдет, если пользователь совершит параллельно еще одну такую же операцию в другом браузере, или в другом экземпляре приложения (или просто увеличим значение Value в базе перед тем, как выполнить context.SubmitChanges() на единицу)? Как и ожидалось, мы получим Exception. Но как linq to sql узнал о том, что запись в базе изменилась? И, самое, главное – как записать актуальные записи в базу? Для этого выведем запросы, которые идут в базу в лог. Немного подправим наш код следующим образом:
using(var context = new DataBaseObjectsDataContext(connectionString)){
context.Log = new StreamWriter("D:\\log.txt");
context.UserStatistics
.Where(item => item.Type == 2)
.First()
.Value = value;
context.SubmitChanges();
context.Log.Dispoce();
}
Оказывается, в базу уходит такой запрос на update:
UPDATE [DBO].[UserStatistic]
SET [Value] = @p5
WHERE ([ID] = @p0) and ([UserID] = @p1) and ([DateCollected] = @p2) and
([Type] = @p3) and ([Value] = @p4)
-- @p0: Input BigInt (Size = 0; Prec = 0; Scale = 0) [6]
-- @p1: Input BigInt (Size = 0; Prec = 0; Scale = 0) [3]
-- @p2: Input Date (Size = 0; Prec = 0; Scale = 0) [22.01,2011 0:00:00]
-- @p3: Input Int (Size = 0; Prec = 0; Scale = 0) [2]
-- @p4: Input Int (Size = 0; Prec = 0; Scale = 0) [11]
-- @p5: Input Int (Size = 0; Prec = 0; Scale = 0) [15]
-- Context: SqlProvider(Sql2008) Model:AttributedMetaModel Build 3.5.30729.1
Т.е. мы пытаемся обновить все записи в базе, которые имеют значения, аналогичные тому, которые были в сущности до изменения Value. Легко представить, что если бы табличка имела несколько другой вид, то мы могли бы изменить не одну, а сразу несколько записей… Чтобы это исправить, необходимо в дизайнере класса установить для всех полей, значения которых не нужно проверять при модификации данных, свойство UpdateCheck. В нашем случае достаточно установит UpdateCheck = Never для всех полей, кроме первичного ключа.
Теперь данные успешно обновляются, пользователь всем доволен, а в базу уходит такой запрос:
SELECT TOP(1) [t0].[ID], [t0].[UserID], [t0].[DateCollected], [t0].[Type], [t0].[Value]
FROM [dbo].[UserStatistic] as [t0]
WHERE [t0].[Type] = @p0
-- @p0: Input Int (Size = 0; Prec = 0; Scale = 0) [2]
-- Context: SqlProvider(Sql2008) Model:AttributedMetaModel Build 3.5.30729.1
P.S. Другой вариант, с использованием перегруженного метода SubmitChanges, который принимает на вход параметр ConflictMode – нам не подходит, так как он успешно выполнит весь код, нигде не упадет, но и записи в базе не обновит.
2. Почему данные не вставляются?
Пусть теперь у нас несколько другой сценарий – мы собираем статистику обо всех выполненных пользователем действиях, а не только о последней операции каждого типа. Кроме того, для наших нужд переопределим метод Equals для сущности UserStatistic (например, мы хотим сравнивать значения всех полей сущности):
// просто переопределили Equals и GetHashCode для сущности UserStatistic
partial class Userstatistic {
public ovverride bool Equals(object obj){
if(ReferenceEquals(null, obj)){
return false;
}
if(referenceEquals(this, obj)){
return true;
}
return obj.GetType() == typeof(UserStatistic)
&& Equals((UserStatistic) obj);
}
public bool Equals(UserStatistic other){
if(ReferenceEquals(null, obj)){
return false;
}
if(referenceEquals(this, obj)){
return true;
}
return other.ID == ID && other.UserID == UserID
&& other.DateCollected.Equals(DateCollected)
&& other.Type == Type && other.Value == Value;
}
public ovveride int GetHashCode(){
int result = UserID.GetHashCode();
result = (result * 397) ^ ID.GetHashCode();
result = (result * 397) ^ DateCollected.GetHashCode();
result = (result * 397) ^ Type.GetHashCode();
result = (result * 397) ^ Value.GetHashCode();
return result;
}
}
А затем – самое интересное: пусть мы определили, что пользователь в один день выполнил два одинаковых действия с одинаковым результатом, и собираемся сохранить эту информацию об обоих действиях в базу:
var context = new DataBaseObjectsDataContext(connectionString);
// пользователь выполнил два таких действия
var s1 = new UserStatistic() {
DateCollected = DateTime.Today,
UserID = 1,
Type = 2,
Value = 1
}
var s2 = new UserStatistic() {
DateCollected = DateTime.Today,
UserID = 1,
Type = 2,
Value = 1
}
// сохраняем результат в базу
var insertonCommit = new List(2);
insertonCommit.Addrange(new[] {s1, s2});
context.UserStatistics.InsertAllOnSubmit(insertonCommit);
context.SubmitChanges();
Запустим код, и мы увидим, что вставилась только одна запись:
Более того, если мы попытаемся обновить данные в базе – мы получим исключение: Incorrect syntax near the keyword "WHERE"
// обновим запись в базе
var context = new dataBaseObjectsDataContext(connectionString);
context.UserStatistics.Where(s => s.ID == 8).First().Value = 20;
context.SubmitChanges(); // throws SqlException: incorrect syntax near the keyword "WHERE"
А в базу уходит вот такой вот интересный запрос:
UPDATE [dbo].[UserStatistic]
SET
WHERE [ID] = @p0
-- @p0: Input BigInt (Size = 0; Prec = 0; Scale = 0) [8]
-- Context: SqlProvider(Sql2008) Model:AttributedMetaModel Build 3.5.30729.1
Что же случилось, кто виноват, и самое главное - что делать? Для этого посмотрим, как работают методы InsertAllOnSubmit и SubmitChanges (здесь могла быть реклама рефлектора). Метод InsertAllOnSubmit в цикле для каждой сущности из переданного списка вызывает InsertOnSubmit, тело которого имеет вид:
public void InsertOnSubmit(TEntity entity) {
if (entity == null) {
throw Error.ArgumentNull("entity");
}
CheckReadOnly();
context.CheckNotInSubmitChanges();
context.VerifyTrackingEnabled();
MetaType type = this.metaTable.RowType.GetInheritanceType(entity.GetType());
if (!IsTrackableType(type)) {
throw Error.TypeCouldNotBeAdded(type.Type);
}
TrackedObject tracked = this.context.Services.ChangeTracker.GetTrackedObject(entity);
if (tracked == null) {
tracked = this.context.Services.ChangeTracker.Track(entity);
tracked.ConvertToNew();
} else if (tracked.IsWeaklyTracked) {
tracked.ConvertToNew();
} else if (tracked.IsDeleted) {
tracked.ConvertToPossiblyModified();
} else if (tracked.IsRemoved) {
tracked.ConvertToNew();
} else if (!tracked.IsNew) {
throw Error.CantAddAlreadyExistingItem();
}
}
Мы видим, что для каждой сущности вызывается метод GetTrackedObject, и в зависимости от полученного значения - запись либо будет вставлена в базу, либо будет выброшено исключение, либо сущность будет проигнорирована… Теперь посмотрим, что возвращает данные метод для сущности s2, после того как была вставлена сущность s1:
var context = new DataBaseObjectsDataContext(connectionString);
// пользователь выполнил два таких действия
var s1 = new UserStatistic() {
DateCollected = DateTime.Today,
UserID = 1,
Type = 2,
Value = 1
}
var s2 = new UserStatistic() {
DateCollected = DateTime.Today,
UserID = 1,
Type = 2,
Value = 1
}
// сохраняем результат в базу
context.UserStatistics.InsertOnSubmit(s1);
context.SubmitChanges();
PropertyInfo piSercices = context.GetType().GetProperty("Services", GetBindingFlags());
Object services = piServices.Getvalue(context, null);
PropertyInfo piChangeTracker = services.GetType().GetProperty("ChangeTracker", GetBindingFlags());
Object changeTracker = piChangeTracker.GetValue(services, null);
MethodInfo getTrackedObjectMethod = changeTracker.GetType().GetMethod("GetTrackedObject", GetBindingFlags());
object res = getTrackedObjectMethod.Invoke(changeTracker, new object[] {s2});
Как видно, из скриншотов, сущность не будет записана в базу, так как считается, что она уже вставлена и не изменялась, и не подпадает ни под одно условие к коде. Аналогично и при обновлении – у сущности просто не будет полей помеченных для модификации. Так что нужно быть очень аккуратным при переопределении стандартных методов для автогенерируемых с помощью linq to sql классов.
3. Почему мои данные не читаются?
Предположим, что у нас еще один сценарий, в котором есть два контекста, один из которых только пишет в базу, а другой – только читает из нее. Пусть первый контекст, считает некоторые данные, второй обновит данные в базе, которые были считаны первым контекстом, а, затем, первый контекст попытается снова считать эти данные из базы:
var readingContext = new DataBaseObjectsDataContext(connectionString);
var writingContext = new DataBaseObjectsDataContext(connectionString);
// здесь результат равен 1
int firstReadedValue = readingContext.UserStatistics
.Where(s => s.ID == 15)
.First()
.Value();
// обновим запись в базе
writingContext.UserStatistics.Where(s => s.ID == 15).First().Value = 20;
writingContext.SubmitChanges();
// здесь результат опять будет равен 1 :(
int secondReadedValue = readingContext.UserStatistics
.Where(s => s.ID == 15)
.First()
.Value;
Как видим, будут считаны устаревшие данные. Причина заключается в том, контекст по умолчанию кеширует обрабатываемые данные. И чтобы решить данную проблему – можно явно запретить кеширование для всего контекста в целом (это снизит производительность, зато у нас всегда будут актуальные данные):
var readingContext = new DataBaseObjectsDataContext(connectionString);
var writingContext = new DataBaseObjectsDataContext(connectionString);
readingContext.ObjectTrackingEnabled = false;
Другой вариант решения данной проблемы – обновления сущности через метод Refresh:
var readingContext = new DataBaseObjectsDataContext(connectionString);
var writingContext = new DataBaseObjectsDataContext(connectionString);
// здесь результат равен 1
var oldEntity = readingContext.UserStatistics.Where(s => s.ID == 15).First();
int firstReadedValue = oldEntity.Value();
// обновим запись в базе
writingContext.UserStatistics.Where(s => s.ID == 15).First().Value = 20;
writingContext.SubmitChanges();
// теперь результат будет равен 20
readingContext.Refresh(RefreshMode.OverwriteCurrentValues, oldEntity);
int secondReadedValue = oldEntity.Value;
4. Почему оно не работает?
Предположим, у нас есть некая хранимая процедура, которая написана с помощью dynamic sql – которая будет просто возвращать все записи из базы, у которых значение UserId совпадает с переданным параметром:
create procedure [dbo].[GetUsersStatistics](
@userId bigint
)
as
begin
declare @sql nvarchar(max) = 'select Type, Value from UserStatistic
where UserId = ' + cast(@userId as nvarchar(8));
exec(@sql);
end
Попытаемся, вызвать ее с помощью linq to sql. Для этого добавим данную хранимую процедуру в наш проект. И первое, что нас должно насторожить – тип возвращаемого значения – он указан как None:
А если выполнить этот небольшой код, то получим, что результат вызова равен 0:
var context = new DataBaseObjectsDataContext(connectionString);
// здесь res будет равен 0
var res = context.sp_GetUsersStatistics(1);
В чем же дело? Как оказалось – сигнатура возвращаемого хранимой процедурой значения определяется в момент добавления ее в проект через дизайнер. Причем сама хранимая процедура в этот момент не выполняется, а лишь анализируется на предмет возвращаемого значения и входных параметров. Существует два варианта решения этой проблемы:
-
Создадим фейковую хранимую процедуру, которая возвращает такой же набор полей, как и основная. Так как сигнатура определяется только один раз – то после добавления ее в проект, мы спокойно можем заменить код хранимой процедуры в базе.
-
Но более правильным решением, является все-таки небольшое изменение кода хранимой процедуры, чтобы подсказать дизайнеру тип возвращаемого значения:
create procedure [dbo].[GetUsersStatistics](
@userId bigint
)
as
begin
declare @sql nvarchar(max) = 'select Type, Value from UserStatistic
where UserId = ' + cast(@userId as nvarchar(8));
exec(@sql);
return;
select 0 as Type, 0 as Value;
end
И теперь наша процедура на радость пользователям возвращает правильный результат:
Использованные источники:
- http://www.rsdn.ru/forum/dotnet/3659684.flat.aspx
- Msdn
