Запрос из SQL в EF(LINQ)

@NewSharp · Регистрация: 11.01.2022

Студворк — интернет-сервис помощи студентам

Доброго времени суток!
Не как не могу понять как реализовать запрос к БД из Entity Framework 7.0, .net core 6
Есть сущность в БД:

Кликните здесь для просмотра всего текста

C#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
/// <summary> Тип операции (приход=25, расход=26) </summary>
    private TypeDoc _typeDoc = null!;
    public TypeDoc TypeDoc { get => _typeDoc; set => Set(ref _typeDoc, value); }
 
    /// <summary> Дата записи </summary>
    private DateTime _dateRegister;
    public DateTime DateRegister { get => _dateRegister; set => Set(ref _dateRegister, value); }
 
    /// <summary> Родительский документ поступения </summary>
    private ComingTmc? _comingTmc;
    public ComingTmc? ComingTmc { get => _comingTmc; set => Set(ref _comingTmc, value); }
 
    /// <summary> Товар </summary>
    private Tmc _tmc = null!;
    public Tmc Tmc { get => _tmc; set => Set(ref _tmc, value); }
 
    /// <summary> Единица измерения </summary>
    private UnitOkei _unitOkei = null!;
    public UnitOkei UnitOkei { get => _unitOkei; set => Set(ref _unitOkei, value); }
 
    /// <summary> Количество товара </summary>
    private decimal _quantity;
    public decimal Quantity { get => _quantity; set => Set(ref _quantity, value); }
 
    /// <summary> Цена товара </summary>
    private decimal _price;
    public decimal Price { get => _price; set => Set(ref _price, value); }
 
    /// <summary> Сумма по позиции (сумма для учета ТМЦ) </summary>
    private decimal _amount;
    public decimal Amount { get => _amount; set => Set(ref _amount, value); }
 
    /// <summary> Сумма НДС по позиции </summary>
    private decimal _amountNds;
    public decimal AmountNds { get => _amountNds; set => Set(ref _amountNds, value); }
 
    private AccountingPlan _debit = null!;
    public AccountingPlan Debit { get => _debit; set => Set(ref _debit, value); }
 
    private AccountingPlan _credit = null!;
    public AccountingPlan Credit { get => _credit; set => Set(ref _credit, value); }
    
    /// <summary> Место хранения ТМЦ </summary>
    private StorageLocation _storageLocation = null!;
    public StorageLocation StorageLocation { get => _storageLocation; set => Set(ref _storageLocation, value); }
 
    private string? _description;
    public string? Description { get => _description; set => Set(ref _description, value); }

в БД выглядит так:

Кликните здесь для просмотра всего текста

По задумке нужно получить остатки ТМЦ, т.е сгруппировать по свойству Tmc, StorageLocation, и получить разность Quantity в зависимости от TypeDocId 25=приход, 26=расход
в SQL запрос выглядит так

Кликните здесь для просмотра всего текста

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT t.TmcId, tmc.Name,
(SUM(t.Quantity) - ifnull((SELECT SUM(t2.Quantity) 
FROM Agrodb.TmcRegisters AS t2 
WHERE t2.TmcId=t.TmcId AND t2.TypeDocId=26 AND t.DebitId=t2.CreditId AND 
t2.StorageLocationId=t.StorageLocationId ), 0)) AS Quantity, 
(SUM(t.Amount) - ifnull((SELECT SUM(t2.Amount) 
FROM Agrodb.TmcRegisters AS t2 WHERE t2.TmcId=t.TmcId AND t2.TypeDocId=26 AND 
t.DebitId=t2.CreditId AND t2.StorageLocationId=t.StorageLocationId ), 0)) AS Amount,
a.Code, s.Name   
FROM Agrodb.TmcRegisters AS t 
INNER JOIN Agrodb.Tmc AS tmc ON t.TmcId=tmc.Id
INNER JOIN Agrodb.AccountingPlans AS a ON t.DebitId=a.Id 
INNER JOIN Agrodb.StorageLocations AS s ON t.StorageLocationId=s.Id
WHERE t.TypeDocId=25
GROUP BY t.StorageLocationId, t.TmcId, t.DebitId;

в итоге должно получится что-то типа такого:

Кликните здесь для просмотра всего текста

Пытался выполнить запрос напрямую из EF

C#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 public async Task<ObservableCollection<TmcSprDto>?> GetAllAsync(CancellationToken cancel = default)
    {
        
        var spr = await _db.TmcRegisters
            .FromSql($"SELECT t.TmcId, tmc.Name,(sum(t.Quantity) - ifnull((SELECT sum(t2.Quantity) FROM Agrodb.TmcRegisters as t2 where t2.TmcId=t.TmcId and t2.TypeDocId=26 and t.DebitId=t2.CreditId and t2.StorageLocationId=t.StorageLocationId ), 0)) as Quantity, (sum(t.Amount) - ifnull((SELECT sum(t2.Amount) FROM Agrodb.TmcRegisters as t2 where t2.TmcId=t.TmcId and t2.TypeDocId=26 and t.DebitId=t2.CreditId and t2.StorageLocationId=t.StorageLocationId ), 0)) as Amount, a.Code, s.Name  FROM Agrodb.TmcRegisters as t inner join Agrodb.Tmc as tmc on t.TmcId=tmc.Id inner join Agrodb.AccountingPlans as a on t.DebitId=a.Id inner join Agrodb.StorageLocations as s on t.StorageLocationId=s.Id where t.TypeDocId=25 group by t.StorageLocationId, t.TmcId, t.DebitId;")
            .ToArrayAsync(cancel).ConfigureAwait(false);
        var col = new ObservableCollection<TmcSprDto>();
       foreach (var tmcReg in spr)
       {
           col.Add(new TmcSprDto()
           {
               NameTmc =tmcReg.Tmc.Name,
               Article = tmcReg.Tmc.ArticleNumber!,
               Unit = tmcReg.UnitOkei.Abbreviation,
               Quantity = tmcReg.Quantity,
               Price = tmcReg.Price,
               Amount = tmcReg.Amount,
               AccountingPlanCode = tmcReg.Debit.Code,
               StorageLocation = tmcReg.StorageLocation.Name,
           });
       }
 
       return col;
    }

Выдает ошибку :"The required column 'Id' was not present in the results of a 'FromSql' operation"
Если в запрос добавить отбор Id то он соответственно не будет выдавать то, что мне нужно.

Помогите разобраться, как такое реализуется в EF или LINQ, т.к. в будущем предполагается много использовать таких запросов.

@Dr9vik · 24.02.2023, 19:20

не использовать EF

Сообщение от NewSharp

"The required column 'Id' was not present in the results of a 'FromSql' operation"

и это починить нельзя, но можно обмануть добавив Id потом с рандомными ключами

@NewSharp · 24.02.2023, 21:49 **[ТС]**

Может можно разбить на несколько запросов? Простоя не могу сообразить как.

@Dr9vik · 24.02.2023, 22:15

Сообщение от NewSharp

Может можно разбить на несколько запросов? Простоя не могу сообразить как.

не знаю что за база у вас
в постгресе есть команда With
https://postgrespro.ru/docs/po... eries-with
позволяет избавиться от джоинов

@NewSharp · 24.02.2023, 23:09 **[ТС]**

Сообщение от Dr9vik

в постгресе есть команда With

У меня БД MySQL, там нет команды With

@Dr9vik · 24.02.2023, 23:34

Сообщение от NewSharp

У меня БД MySQL, там нет команды With

https://dev.mysql.com/doc/refman/8.0/en/with.html

@NewSharp · 25.02.2023, 09:36 **[ТС]**

Я имел введу не сам MySQL, а Entity Framework.
Проект использует EF и мне нужно решить задачу используя его или LINQ

@Kazbek17 · 25.02.2023, 10:42

Сообщение от NewSharp

Выдает ошибку :"The required column 'Id' was not present in the results of a 'FromSql' operation"

по мне так кажется, что EF не видит данное свойство Id и за алиаса(AS), вот и лупит исключение.

@NewSharp · 25.02.2023, 10:46 **[ТС]**

Доброго времени суток!
Пока проблему решил следующим образом:

C#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
 public async Task<ObservableCollection<TmcSprDto>?> GetAllAsync(CancellationToken cancel = default)
    {
 
        var spr = await _db.TmcRegisters
            .Include(t=>t.Tmc)
            .Include(t=>t.StorageLocation)
            .Include(t=>t.UnitOkei)
            .Include(t=>t.TypeDoc)
            .Include(t=>t.Debit)
            .Include(t=>t.Credit)
            .ToArrayAsync(cancel).ConfigureAwait(false);
 
 
        var reg1 = spr
            .Where(t => t.TypeDoc.Id == 25)
            .GroupBy(t=> new {t.Tmc, t.Debit, t.StorageLocation, t.UnitOkei});
 
        var col = new ObservableCollection<TmcSprDto>();
        foreach (var tm in reg1)
        {
            col.Add(new TmcSprDto()
            {
                Id = tm.Key.Tmc.Id,
                NameTmc = tm.Key.Tmc.Name,
                Article = tm.Key.Tmc.ArticleNumber!,
                Unit = tm.Key.UnitOkei.Abbreviation,
                Quantity = 
                    (spr
                        .Where(s=>s.TypeDoc.Id==25)
                        .Where(s => s.Tmc == tm.Key.Tmc)
                        .Where(s => s.Debit == tm.Key.Debit)
                        .Where(s => s.StorageLocation == tm.Key.StorageLocation)
                        .Where(s => s.UnitOkei == tm.Key.UnitOkei)
                        .Sum(s=>s.Quantity)) - (spr
                        .Where(s => s.TypeDoc.Id == 26)
                        .Where(s => s.Tmc == tm.Key.Tmc)
                        .Where(s => s.Credit == tm.Key.Debit)
                        .Where(s => s.StorageLocation == tm.Key.StorageLocation)
                        .Where(s => s.UnitOkei == tm.Key.UnitOkei)
                        .Sum(s=>s.Quantity)),
                Amount = (spr
                    .Where(s => s.TypeDoc.Id == 25)
                    .Where(s => s.Tmc == tm.Key.Tmc)
                    .Where(s => s.Debit == tm.Key.Debit)
                    .Where(s => s.StorageLocation == tm.Key.StorageLocation)
                    .Where(s => s.UnitOkei == tm.Key.UnitOkei)
                    .Sum(s => s.Amount)) - (spr
                    .Where(s => s.TypeDoc.Id == 26)
                    .Where(s => s.Tmc == tm.Key.Tmc)
                    .Where(s => s.Credit == tm.Key.Debit)
                    .Where(s => s.StorageLocation == tm.Key.StorageLocation)
                    .Where(s => s.UnitOkei == tm.Key.UnitOkei)
                    .Sum(s => s.Amount)),
                AccountingPlanCode = tm.Key.Debit.Code,
                StorageLocation = tm.Key.StorageLocation.Name,
            });
        }
        return col;
    }

Считает пока правильно в разрезе мест хранения и счетов учета.
Временно подойдет. Как говорится нет ни чего постояннее временного.

Остается вопрос в производительности. Пока в таблице записей мало работает достаточно быстро, но боюсь когда записей будет > 1 млн., скорость будет желать лучшего.

Может есть у кого соображения по поводу как хранить остатки ТМЦ, что бы можно было легко и быстро их вычислять?

Подумываю добавить еще оду таблицу и в нее записывать текущий остаток ТМЦ полученный в результате операции движения ТМЦ, но не будет ли это избыточным?

Новые блоги и статьи Все статьи Все блоги /
[Owen Logic] Поддержание уровня воды в резервуаре количеством включённых насосов: моделирование и выбор регулятора ФедосеевПавел 14.03.2026 Поддержание уровня воды в резервуаре количеством включённых насосов: моделирование и выбор регулятора ВВЕДЕНИЕ Выполняя задание на управление насосной группой заполнения резервуара,. . .	делаю науч статью по влиянию грибов на сукцессию anaschu 13.03.2026 прикрепляю статью	SDL3 для Desktop (MinGW): Создаём пустое окно с нуля для 2D-графики на SDL3, Си и C++ 8Observer8 10.03.2026 Содержание блога Финальные проекты на Си и на C++: hello-sdl3-c. zip hello-sdl3-cpp. zip Результат:	Установка CMake и MinGW 13.1 для сборки С и C++ приложений из консоли и из Qt Creator в EXE 8Observer8 10.03.2026 Содержание блога MinGW - это коллекция инструментов для сборки приложений в EXE. CMake - это система сборки приложений. Здесь описаны базовые шаги для старта программирования с помощью CMake и. . .
Как дизайн сайта влияет на конверсию: 7 решений, которые реально повышают заявки Neotwalker 08.03.2026 Многие до сих пор воспринимают дизайн сайта как “красивую оболочку”. На практике всё иначе: дизайн напрямую влияет на то, оставит человек заявку или уйдёт через несколько секунд. Даже если у вас. . .	Модульная разработка через nuget packages DevAlt 07.03.2026 Сложившийся в . Net-среде способ разработки чаще всего предполагает монорепозиторий в котором находятся все исходники. При создании нового решения, мы просто добавляем нужные проекты и имеем. . .	Модульный подход на примере F# DevAlt 06.03.2026 В блоге дяди Боба наткнулся на такое определение: В этой книге («Подход, основанный на вариантах использования») Ивар утверждает, что архитектура программного обеспечения — это структуры,. . .	Управление камерой с помощью скрипта OrbitControls.js на Three.js: Вращение, зум и панорамирование 8Observer8 05.03.2026 Содержание блога Финальная демка в браузере работает на Desktop и мобильных браузерах. Итоговый код: orbit-controls-threejs-js. zip. Сканируйте QR-код на мобильном. Вращайте камеру одним пальцем,. . .

@NewSharp 4 / 2 / 3 Регистрация: 11.01.2022 Сообщений: 24
	24.02.2023, 21:49 [ТС]
	Может можно разбить на несколько запросов? Простоя не могу сообразить как. 0

@NewSharp 4 / 2 / 3 Регистрация: 11.01.2022 Сообщений: 24
	25.02.2023, 09:36 [ТС]
	Я имел введу не сам MySQL, а Entity Framework. Проект использует EF и мне нужно решить задачу используя его или LINQ 0