Берем LinqToDB.EntityFrameworkCore и вытираем все пункты почему мы не можем использовать LINQ, включая CTE, хинты и плохо сгенерированный SQL. Да и не в LINQ дело, а в LINQ провайдере, коим является EF Core.
Так, что же я сделал у нас... Я научил EF Core сохранять старые процедуры прямо в snapshot файлах. Это не сложно, для модели можно навесить атрибуты во время OnModelCreating, обвесив это красивыми расширениями. Это может выглядеть так modelBuilder.HasStoredProcedure("Some", @"CREATE OR ALTER Some ....").
Что же дальше, создаем свой IMigrationsModelDiffer - для каждого провайдера своя реализация и надо от нее наследоваться. При правильной ее регистрации и реализации можно запросто получить автоматическую генерацию миграций вверх и вниз. Сравниваются две модели старая и новая - в старой нет - создаем процедуру, в новой нет - скрипт на удаление, поменялась - делаем обновление.
Теперь если надо изменить процедуру, меняем ее код прямо в C# файле + Add-Migration ProcedureChanged и миграции создаются.
Бенефит ту так же в том что процедуры могут генерироваться динамически используя структуру таблиц, и вот если кто-то изменит определение таблицы, тут же генерируется новая миграция вместе с кодом отката.
Используя этот подход можно и представления включать и много чего друго, скажем так: команда расслабилась, все работает автоматически и координируется в одном месте. Не надо ресурсы сохранять, держать рядом папочку со скриптами и так далее...
Ну и немаловажный бенефит context.Database.EnsureCreated() - запускает все вместе, создает таблицы и процедуры одним скопом, при чем без явных миграционных файлов.
Существенная ошибка в визиторе: должно быть return Expression.Multiply(Visit(node.Left), Visit(node.Right));.
Для справки: у Microsoft есть библиотека Nuqleon, которая содержит множество готовых визиторов для оптимизации, сериализации и других целей. Очень полезный справочный материал.
После группировки для фильтрации можно и не отходить от Query Syntax:
var filteredGroups =
from product in products
join category in categories on product.CategoryId equals category.Id
group product by category.Name into productGroup
select new
{
CategoryName = productGroup.Key,
AveragePrice = productGroup.Average(p => p.Price)
} into s
where s.AveragePrice > 100
select s;
Какой-то ужас при использовании асинхронности. Получается окно апдейтнут из другого потока? А что будет если после появления окна кто-то что-то нажмет и оно полезет в базу? Думаю свалится сразу.
Нужно было взять для перезаливки данных расширение linq2db.EntityFrameworkCore - там легко делается пайплайн из исходной таблицы и вставка в таргет. Вставка присходит быстро и не нужно дополнительных телодвижений.
Приблизительный пример как это могло быть:
using var sourceContext = new MyDbContext(sourceOptions);
using var targetContext = new MyDbContext(targetOptions);
targetContext.BulkCopy(sourceContext.SomeTable.AsNoTracking().AsEnumerable());
targetContext.BulkCopy(sourceContext.SomeOtherTable.AsNoTracking().AsEnumerable());
... // и тд.
Можно сделать обобщенные фукции которые могут заливать инкрементарно или перезапускать заливку если данные в таблице есть, а количество не совпадает.
Также ничего не мешает сериализировать классы в файлы и загружать их если источник недоступен.
Если честно. непонятно почему вы модель базы данных выкладываете в API. Я не верю что у вас нет навигационных свойств которые не должны попадать в API.
По нормальному надо делать DTO, в вашем случае PersonDTO
Ой что-то я сомневаюсь в текущих способностях CH, слишком молодой. Пишем для него провайдер для linq2db, ограничений пока хватает, может и поборем часть ограничений эмуляцией.
Думаю из минусов CockroachDB, все упрется в аналитические выборки. Конечно, это может и не ваш вариант, но как бы хотелось чтобы база и считала быстро.
Стоит также посмотреть и сюда https://www.singlestore.com. Такой же непотопляемый черт, петабайт ready, но с чертовски скоростной аналитикой. Платный однако. Накормить его данными можно кучей способов и очень быстро.
Вот отсюда поподробней. Естественно парсинг дерева выражения еще та задачка, и перый запуск имеет прогиб в скорости. Но и возможности LINQ в плане модификации запросов огромны. Пишешь раз - используешь всюду.
Вот я так и тестирую запросы во время исполнения. Так как разбираю запрос на маленькие и каждую часть спокойно могу проверить на правильность выборки.
Если уж вы ищите легковесные ORM, то не проходите мимо linq2db. LINQ превращает все что вы тут сгенерили в легкую прогулку. LINQ запро на копирование, удаление тоже возможен.
Не забываем и NewSQL базы. Очень впечатлил SingleStore (бывший MemSQL). Когда PG начал тупить, пришлось попробовать альтернативы. И я был приятно удивлен скоростью трехэтажных запросов, где PG просто заставлял идти делать чай между попытками заставить его паралелить запросы, ведь ему дали 40 ядер, а он использует одно.
Тем не менее использование транзакционной СУБД в качестве OLAP хранилища всегда будет считаться архитектурной недоработкой
Kaгбы SingleStore (бывший MemSQL) с вами не согласен. Да там тоже есть колоночное хранилище, но и табличное также. Запросы быстры как молния, а time series прикручен как само собой разумеющееся.
В чем с вами согласен, так что PG это не та база данных на которой это надо ганять. Параллелизация запросов там желает лучших времен.
SELECT HR.*
FROM
(
SELECT
HRX.*,
ROW_NUMBER () OVER ( ORDER BY HRX.PROCESS_DATE DESC) AS RN
FROM SOME_TABLE HRX
) HR
JOIN SOME_TABLE2 AD ON AD.ID = HR.ADDRESS_ID
JOIN SOME_TABLE_3 CH ON AD.ID = CH.ADDRESS_B_ID AND CH.ID = 517425
WHERE HR.RN = 1
AND ….
EF Core хвалят те у кого база просто маленькая. На реальных данных они незаметно переходят на хранимки. Скорость генерации сложного запроса в EF Core просто отстойная. Им приходится перелопачивать код чтобы упростить свои визиторы чтобы итерации не гоняли ту же песню миллионы раз.
Об оконных функциях тоже можно забыть. Parameters Sniffing возможен и никак не обходится. DML операции спрятаны через ChangeTracker — еще тот прикол подтянуть 1000 записей чтобы у них поменять одно поле.
Если уж сравнивать SQLAlchemy с .NET ORM, то только с linq2db, он не прячет от вас SQL и быстр как молния. Также он сам проводит оптимизацию запросов вплоть до того что может поотбрасывать незначимые JOIN.
Насколько мне известно,
DISTINCT ONработает в разы быстрее, чем использованиеROW_NUMBER. Поэтому важно учитывать возможности конкретной базы данных.Берем LinqToDB.EntityFrameworkCore и вытираем все пункты почему мы не можем использовать LINQ, включая CTE, хинты и плохо сгенерированный SQL. Да и не в LINQ дело, а в LINQ провайдере, коим является EF Core.
Так, что же я сделал у нас... Я научил EF Core сохранять старые процедуры прямо в snapshot файлах. Это не сложно, для модели можно навесить атрибуты во время
OnModelCreating, обвесив это красивыми расширениями. Это может выглядеть такmodelBuilder.HasStoredProcedure("Some", @"CREATE OR ALTER Some ....").Что же дальше, создаем свой
IMigrationsModelDiffer- для каждого провайдера своя реализация и надо от нее наследоваться. При правильной ее регистрации и реализации можно запросто получить автоматическую генерацию миграций вверх и вниз. Сравниваются две модели старая и новая - в старой нет - создаем процедуру, в новой нет - скрипт на удаление, поменялась - делаем обновление.Теперь если надо изменить процедуру, меняем ее код прямо в C# файле +
Add-Migration ProcedureChangedи миграции создаются.Бенефит ту так же в том что процедуры могут генерироваться динамически используя структуру таблиц, и вот если кто-то изменит определение таблицы, тут же генерируется новая миграция вместе с кодом отката.
Используя этот подход можно и представления включать и много чего друго, скажем так: команда расслабилась, все работает автоматически и координируется в одном месте. Не надо ресурсы сохранять, держать рядом папочку со скриптами и так далее...
Ну и немаловажный бенефит
context.Database.EnsureCreated()- запускает все вместе, создает таблицы и процедуры одним скопом, при чем без явных миграционных файлов.Существенная ошибка в визиторе: должно быть
return Expression.Multiply(Visit(node.Left), Visit(node.Right));.Для справки: у Microsoft есть библиотека Nuqleon, которая содержит множество готовых визиторов для оптимизации, сериализации и других целей. Очень полезный справочный материал.
После группировки для фильтрации можно и не отходить от Query Syntax:
Вот я отвечал на StackOverflow: Can I reuse code for selecting a custom DTO object for a child property with EF Core?. Как на меня это самый удобный способ работать без Automapper. DelegateDecompiler так вообще упрощает это дело в разы.
Какой-то ужас при использовании асинхронности. Получается окно апдейтнут из другого потока? А что будет если после появления окна кто-то что-то нажмет и оно полезет в базу? Думаю свалится сразу.
Нужно было взять для перезаливки данных расширение linq2db.EntityFrameworkCore - там легко делается пайплайн из исходной таблицы и вставка в таргет. Вставка присходит быстро и не нужно дополнительных телодвижений.
Приблизительный пример как это могло быть:
Можно сделать обобщенные фукции которые могут заливать инкрементарно или перезапускать заливку если данные в таблице есть, а количество не совпадает.
Также ничего не мешает сериализировать классы в файлы и загружать их если источник недоступен.
Если честно. непонятно почему вы модель базы данных выкладываете в API. Я не верю что у вас нет навигационных свойств которые не должны попадать в API.
По нормальному надо делать DTO, в вашем случае PersonDTO
Ой что-то я сомневаюсь в текущих способностях CH, слишком молодой. Пишем для него провайдер для linq2db, ограничений пока хватает, может и поборем часть ограничений эмуляцией.
Думаю из минусов CockroachDB, все упрется в аналитические выборки. Конечно, это может и не ваш вариант, но как бы хотелось чтобы база и считала быстро.
Стоит также посмотреть и сюда https://www.singlestore.com. Такой же непотопляемый черт, петабайт ready, но с чертовски скоростной аналитикой. Платный однако. Накормить его данными можно кучей способов и очень быстро.
Вот вам общий туториал, наверное лучшее что я видел (тыкать по сценариям использования)
https://tortugaresearch.github.io/DotNet-ORM-Cookbook/ORMs.htm
Вот отсюда поподробней. Естественно парсинг дерева выражения еще та задачка, и перый запуск имеет прогиб в скорости. Но и возможности LINQ в плане модификации запросов огромны. Пишешь раз - используешь всюду.
Вот я так и тестирую запросы во время исполнения. Так как разбираю запрос на маленькие и каждую часть спокойно могу проверить на правильность выборки.
Если уж вы ищите легковесные ORM, то не проходите мимо linq2db. LINQ превращает все что вы тут сгенерили в легкую прогулку. LINQ запро на копирование, удаление тоже возможен.
И тут вы опять попадете в Parameter Sniffing. Тут надо или инлайнить параметры или добавлять к запросу OPTION(...).
Не забываем и NewSQL базы. Очень впечатлил SingleStore (бывший MemSQL). Когда PG начал тупить, пришлось попробовать альтернативы. И я был приятно удивлен скоростью трехэтажных запросов, где PG просто заставлял идти делать чай между попытками заставить его паралелить запросы, ведь ему дали 40 ядер, а он использует одно.
Kaгбы SingleStore (бывший MemSQL) с вами не согласен. Да там тоже есть колоночное хранилище, но и табличное также. Запросы быстры как молния, а time series прикручен как само собой разумеющееся.
В чем с вами согласен, так что PG это не та база данных на которой это надо ганять. Параллелизация запросов там желает лучших времен.
Ну я бы ваш запрос переписал как-то так:
Для .NET стека просто толпень инструметов, хотя бы погуглили. Microsoft Orleans заслуживает особого внимания.
EF Core хвалят те у кого база просто маленькая. На реальных данных они незаметно переходят на хранимки. Скорость генерации сложного запроса в EF Core просто отстойная. Им приходится перелопачивать код чтобы упростить свои визиторы чтобы итерации не гоняли ту же песню миллионы раз.
Об оконных функциях тоже можно забыть. Parameters Sniffing возможен и никак не обходится. DML операции спрятаны через ChangeTracker — еще тот прикол подтянуть 1000 записей чтобы у них поменять одно поле.
Если уж сравнивать SQLAlchemy с .NET ORM, то только с linq2db, он не прячет от вас SQL и быстр как молния. Также он сам проводит оптимизацию запросов вплоть до того что может поотбрасывать незначимые JOIN.