Информация об изменениях

Сообщение Re[65]: Тормознутость и кривость linq от 13.04.2016 7:24

Изменено 13.04.2016 7:25 Serginio1

Старый текст
Новый текст
Разница

Здравствуйте, alex_public, Вы писали:

_>Здравствуйте, Serginio1, Вы писали:

S>>>>>> Хранимые процедуры можно применять и в Linq, в том числе на CLR
_>>>>>Можно. ) Но не нужно (см. пример тех же SO).
S>>>> А где написано, что ХП не используют CLR (.Net)?
_>>>Перечитай внимательно цитату в этом http://rsdn.ru/forum/flame.comp/6412784.1 моём сообщение.
S>> Читал не нашел.
S>>Что бы ло понятно
S>>Поддерживаемые библиотеки платформы .NET Framework
S>>Скалярные функции среды CLR

_>Помогаю слепым в последний раз:
_>

Stack Overflow has only 1 stored procedure in the database and I intend to move that last vestige into code.

Я плохо знаю англицкий. Переведи

We write almost every query (as you would using Stored Procedures), they simply live in code — not hidden in a stored procedure. Also keep in mind that we manage hundreds of copies of the same schema across hundreds of databases (one per site). The pain points I mention above are multiplied in our situation.

I don't think EF/L2S generated code is any better than stored procedures, my personal feelings are that they're both bad for different reasons *for us*. Dapper and raw SQL let's us tune queries the way we want, manage them trivially (all in git, since they're just part of the code), and get all of the wins that stored procedures allow us without the downsides (as I see them) of the abstraction.

Я так понимаю, что они используют пакетные запросы. Но в них можно использовать и CLR

Опять же

Our usage of SQL is pretty simple. Simple is fast. Though some queries can be crazy, our interaction with SQL itself is fairly vanilla. We have some legacy Linq2Sql, but all new development is using Dapper, our open source Micro-ORM using POCOs. Let me put this another way: Stack Overflow has only 1 stored procedure in the database and I intend to move that last vestige into code.

Dapper я так понимаю маппер запросов
https://github.com/StackExchange/dapper-dot-net

Multi Mapping

Dapper allows you to map a single row to multiple objects. This is a key feature if you want to avoid extraneous querying and eager load associations.

Example:

var sql =
@"select * from #Posts p
left join #Users u on u.Id = p.OwnerId
Order by p.Id";

var data = connection.Query<Post, User, Post>(sql, (post, user) => { post.Owner = user; return post;});
var post = data.First();

post.Content.IsEqualTo("Sams Post1");
post.Id.IsEqualTo(1);
post.Owner.Name.IsEqualTo("Sam");
post.Owner.Id.IsEqualTo(99);

important note Dapper assumes your Id columns are named "Id" or "id", if your primary key is different or you would like to split the wide row at point other than "Id", use the optional 'splitOn' parameter.

Multiple Results

Dapper allows you to process multiple result grids in a single query.

Example:

var sql =
@"
select * from Customers where CustomerId = @id
select * from Orders where CustomerId = @id
select * from Returns where CustomerId = @id";

using (var multi = connection.QueryMultiple(sql, new {id=selectedId}))
{
var customer = multi.Read<Customer>().Single();
var orders = multi.Read<Order>().ToList();
var returns = multi.Read<Return>().ToList();
...
}

Stored Procedures

Dapper fully supports stored procs:

var user = cnn.Query<User>("spGetUser", new {Id = 1},
commandType: CommandType.StoredProcedure).SingleOrDefault();

If you want something more fancy, you can do:

var p = new DynamicParameters();
p.Add("@a", 11);
p.Add("@b", dbType: DbType.Int32, direction: ParameterDirection.Output);
p.Add("@c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

cnn.Execute("spMagicProc", p, commandType: CommandType.StoredProcedure);

int b = p.Get<int>("@b");
int c = p.Get<int>("@c");

Re[65]: Тормознутость и кривость linq

Stack Overflow has only 1 stored procedure in the database and I intend to move that last vestige into code.

Я плохо знаю англицкий. Переведи

We write almost every query (as you would using Stored Procedures), they simply live in code — not hidden in a stored procedure. Also keep in mind that we manage hundreds of copies of the same schema across hundreds of databases (one per site). The pain points I mention above are multiplied in our situation.

I don't think EF/L2S generated code is any better than stored procedures, my personal feelings are that they're both bad for different reasons *for us*. Dapper and raw SQL let's us tune queries the way we want, manage them trivially (all in git, since they're just part of the code), and get all of the wins that stored procedures allow us without the downsides (as I see them) of the abstraction.

Я так понимаю, что они используют пакетные запросы. Но в них можно использовать и CLR

Опять же

Our usage of SQL is pretty simple. Simple is fast. Though some queries can be crazy, our interaction with SQL itself is fairly vanilla. We have some legacy Linq2Sql, but all new development is using Dapper, our open source Micro-ORM using POCOs. Let me put this another way: Stack Overflow has only 1 stored procedure in the database and I intend to move that last vestige into code.

Dapper я так понимаю маппер запросов
https://github.com/StackExchange/dapper-dot-net

Multi Mapping

Dapper allows you to map a single row to multiple objects. This is a key feature if you want to avoid extraneous querying and eager load associations.

Example:

var sql =
@"select * from #Posts p
left join #Users u on u.Id = p.OwnerId
Order by p.Id";

var data = connection.Query<Post, User, Post>(sql, (post, user) => { post.Owner = user; return post;});
var post = data.First();

post.Content.IsEqualTo("Sams Post1");
post.Id.IsEqualTo(1);
post.Owner.Name.IsEqualTo("Sam");
post.Owner.Id.IsEqualTo(99);

important note Dapper assumes your Id columns are named "Id" or "id", if your primary key is different or you would like to split the wide row at point other than "Id", use the optional 'splitOn' parameter.

Multiple Results

Dapper allows you to process multiple result grids in a single query.

Example:

var sql =
@"
select * from Customers where CustomerId = @id
select * from Orders where CustomerId = @id
select * from Returns where CustomerId = @id";

using (var multi = connection.QueryMultiple(sql, new {id=selectedId}))
{
var customer = multi.Read<Customer>().Single();
var orders = multi.Read<Order>().ToList();
var returns = multi.Read<Return>().ToList();
...
}

Stored Procedures

Dapper fully supports stored procs:

var user = cnn.Query<User>("spGetUser", new {Id = 1},
commandType: CommandType.StoredProcedure).SingleOrDefault();

If you want something more fancy, you can do:

var p = new DynamicParameters();
p.Add("@a", 11);
p.Add("@b", dbType: DbType.Int32, direction: ParameterDirection.Output);
p.Add("@c", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

cnn.Execute("spMagicProc", p, commandType: CommandType.StoredProcedure);

int b = p.Get<int>("@b");
int c = p.Get<int>("@c");

Кстати про динамики

Execute a query and map it to a list of dynamic objects

public static IEnumerable<dynamic> Query (this IDbConnection cnn, string sql, object param = null, SqlTransaction transaction = null, bool buffered = true)

This method will execute SQL and return a dynamic list.

Example usage:

var rows = connection.Query("select 1 A, 2 B union all select 3, 4");

((int)rows[0].A)
.IsEqualTo(1);

((int)rows[0].B)
.IsEqualTo(2);

((int)rows[1].A)
.IsEqualTo(3);

((int)rows[1].B)
.IsEqualTo(4);