Entity framework Хранимые процедуры

@6elka1703 · Регистрация: 29.10.2016

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

Привет всем, подскажите один момент
у меня есть сущность Заказчик

C#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
public class Customer
    {
        public int Id { get; set; }
 
        [Required(ErrorMessage = "Заполните наименование компании")]
        [Display(Name = "Наименование")]
        public string Name { get; set; }
 
        [Required(ErrorMessage = "Заполните юридический адрес")]
        [Display(Name = "Юридический адрес")]
        public string Address { get; set; }
 
        [NotMapped]
        public int CountProjects { get; set; }
 
        [NotMapped]
        public int CountEndProjects { get; set; }
        
    }
}

и сущность Проект

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
public class Project
    {
        public int Id { get; set; }
 
        [Required(ErrorMessage = "Заполните наименование проекта")]
        [Display(Name = "Наименование")]
        public string Name { get; set; }
 
        [DataType(DataType.Date)]
        [Required(ErrorMessage = "Заполните дату начала проекта")]
        [Display(Name = "Дата начала проекта")]
        public DateTime DateStart { get; set; }
 
        [DataType(DataType.Date)]
        [Required(ErrorMessage = "Заполните планируемую дату завершения проекта")]
        [Display(Name = "Планируемая дата завершения проекта")]
        public DateTime DateEndPlan { get; set; }
 
        [DataType(DataType.Date)]
        [Display(Name = "Фактическая дата завершения проекта")]
        public DateTime DateEndFact { get; set; }
 
        [Required(ErrorMessage = "Заполните фамилию и инициалы менеджера")]
        [Display(Name = "Фамилия И.О. менеджера проекта")]
        public string ManagerName { get; set; }
 
        [Required(ErrorMessage = "Укажите закачкика проекта")]
        [Display(Name = "Заказчки")]
        public int CustomerId { get; set; }
 
        public Customer Customer { get; set; }
    }

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

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE PROCEDURE [dbo].[GetCustomers]
AS
    SELECT 
    Customers.Id,
    Customers.Name, 
    Customers.Address,  
    COUNT(Projects.CustomerId) AS CountProjects,
    SUM(CASE
        WHEN Projects.DateEndFact IS NULL
        THEN 0
        ELSE 1
    END) AS CountEndProjects
    FROM Customers LEFT OUTER JOIN Projects
    ON Customers.Id = Projects.CustomerId
    GROUP BY Projects.CustomerId, Customers.Id, Customers.Name, Customers.Address

вызываю ее так

C#
1
var customers = context.Customers.FromSqlRaw("GetCustomers");

но почему то поля CountProjects и CountEndProjects приходят пустые с нулем. Хотя если процедуру выполнить на sql то запрос возвращает данные как надо.
Что сделал не так?

@6elka1703 · 21.01.2022, 12:03 **[ТС]**

Andrey-MSK,

Сообщение от Andrey-MSK

В классе данных должно быть навигационное свойство на вторую таблицу и объявлен ForeignKey во второй таблице. Тогда вы сможете джойнить таблицы в запросе EF и получать связанные коллекции из второй таблицы.

Так и сделал, в классе project есть ForeignKey

C#
1
2
public int CustomerId { get; set; }
public Customer Customer { get; set; }

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
public class Project
    {
        public int Id { get; set; }
 
        [Required(ErrorMessage = "Заполните наименование проекта")]
        [Display(Name = "Наименование")]
        public string Name { get; set; }
 
        [DataType(DataType.Date)]
        [Required(ErrorMessage = "Заполните дату начала проекта")]
        [Display(Name = "Дата начала проекта")]
        public DateTime DateStart { get; set; }
 
        [DataType(DataType.Date)]
        [Required(ErrorMessage = "Заполните планируемую дату завершения проекта")]
        [Display(Name = "Планируемая дата завершения проекта")]
        public DateTime DateEndPlan { get; set; }
 
        [DataType(DataType.Date)]
        [Display(Name = "Фактическая дата завершения проекта")]
        public DateTime DateEndFact { get; set; }
 
        [Required(ErrorMessage = "Заполните фамилию и инициалы менеджера")]
        [Display(Name = "Фамилия И.О. менеджера проекта")]
        public string ManagerName { get; set; }
 
        [Required(ErrorMessage = "Укажите закачкика проекта")]
        [Display(Name = "Заказчки")]
        public int CustomerId { get; set; }
 
        public Customer Customer { get; set; }

Добавлено через 1 минуту
Andrey-MSK,
только не понимаю, зачем в классе хранить коллекцию подчиненной таблицы?

C#
1
public ICollection<DrawSystem> DrawSystems { get; set; }

@Andrey-MSK · 21.01.2022, 12:06

Сообщение от 6elka1703

только не понимаю, зачем в классе хранить коллекцию подчиненной таблицы?

Загрузка связанных данных

@6elka1703 · 21.01.2022, 12:12 **[ТС]**

Andrey-MSK, почитаю про загрузку связанных данных. спасибо.

@6elka1703 · 22.01.2022, 16:23 **[ТС]**

Подскажите пожалуйста еще один момент,

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

Customer и Project связаны по FK, в классе Customer есть навигационное свойство на проекты

C#
1
public List<Project> Projects { get; set; } = new List<Project>();

C#
1
2
3
4
5
6
7
8
9
10
11
12
13
 var customer = context.Customers
                .Include(x => x.Projects)
                .SelectMany(x => x.Projects.DefaultIfEmpty(), (cust, prj) => new { cust, prj })
                .GroupBy(x => new { prjId = x.prj.CustomerId, custId = x.cust.Id, x.cust.Name, x.cust.Address })
                .Select(x => new Customer
                {
                    Id = x.Key.custId,
                    Name = x.Key.Name,
                    Address = x.Key.Address,
                    CountProjects = x.Count(g => g.prj.Id != null),
                    CountEndProjects = x.Sum(s => s.prj.DateEndFact != null ? 1 : 0),
                    Projects = (List<Project>)x.Select(x => x.cust.Projects)
                }).FirstOrDefault(x => x.Id == id);

@Usaga · 23.01.2022, 09:34

Сообщение от 6elka1703

но такой запрос выбивает ошибку

Какую именно - секрет фирмы?

@6elka1703 · 23.01.2022, 16:16 **[ТС]**

Usaga,

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

Какую именно - секрет фирмы?

Извиняюсь, чет сразу не додумался прикрепить.

System.InvalidOperationException: "Processing of the LINQ expression 'GroupByShaperExpression:
KeySelector: new {
prjId = p.CustomerId,
custId = c.Id,
Name = c.Name,
Address = c.Address
},
ElementSelector:new {
cust = EntityShaperExpression:
EntityType: Customer
ValueBufferExpression:
ProjectionBindingExpression: cust
IsNullable: False
,
prj = EntityShaperExpression:
EntityType: Project
ValueBufferExpression:
ProjectionBindingExpression: prj
IsNullable: True
}
' by 'RelationalProjectionBindingExpressionVi sitor' failed. This may indicate either a bug or a limitation in Entity Framework. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information."

@6elka1703 · 24.01.2022, 15:21 **[ТС]**

Подскажите один момент пожалуйста.
Создал хранимую процедуру (да, я понимаю что хранимка мне тут по логике не нужна, но такое задание) с таким запросом

SQL
1
2
3
4
5
6
7
CREATE PROCEDURE [dbo].[GetProjectsByCustomerId]
    @customerId INT
AS
    SELECT p.Id, p.Name, p.DateEndFact, p.DateEndPlan, p.DateStart, p.ManagerName, p.CustomerId, c.Id, c.Name, c.Address
    FROM Projects AS p
    LEFT JOIN Customers AS c ON p.CustomerId = c.Id
    WHERE p.CustomerId = @customerId

Хочу получить проекты клиента и при этом присоединить связанные данные по клиенту.
В сущности Project есть FK на таблицу Customers.
Но из-за того что некоторые поля в обоих таблицах имеют одинаковое наименование, мне выдает ошибку.
Подскажите как правильно разыменовать поля чтобы на выходе получить объект Project и внутри еще объект Customer?

@MsGuns · 24.01.2022, 17:25

SQL
1
SELECT p.Id AS PId, p.Name AS PName, p.DateEndFact, p.DateEndPlan, p.DateStart, p.ManagerName, p.CustomerId, c.Id AS CId, c.Name AS CName, c.[Address]

Добавлено через 1 минуту
Спрошу еще раз : зачем хранимка для простого запроса ?

@Andrey-MSK · 24.01.2022, 17:31

6elka1703, я же вам давал ссылку на почитать про связанные данные в запросах EF. Вы её прочитали?

@MsGuns · 24.01.2022, 17:46

Сообщение от 6elka1703

на выходе получить объект Project и внутри еще объект Customer?

Увы, в Вашем случае этого не получится. Хранимка возвращает один НД, который будет завернут в один "объект", в котором будет информация и о проекте, и о клиенте. Этот объект и будет представлен в Модели EF.

Данные об объекте Customer есть в самом объекте Project, точнее должны быть. Они представлены строкой public virtual Customer в классе модели Project. Когда Вы выбираете из DbSet<Project> объект(ы) Project, то в этом поле и будет(будут) лежать объект(ы) Customer. Никаких дополнительных выборок делать не нужно,- EF все уже выгребет

Попытки завернуть все это в хранимку есть ничто иное, как пытаться ехать на авто на конной тяге.
Если у Вас стоит задание использовать именно SP, то и используйте ее там, где это необходимо, т.е. невозможно сделать "штатными" средствами EF.

Добавлено через 8 минут
Теперь о хранимке.
С ее помощью (в отличите от UDF, View и EF) можно получить любое количество "объектов", т.е. наборов данных.
Каждый из них и будет коллекцией "объектов". Для того, чтобы получить их все на клиенте (EF), нужно их обработать последовательным перебором (вроде как ObjectQuery.NextResult, но могу ошибиться - давно делал).
У меня лично с этим был полный "эпик фейл", т.к. EF во-первых, не видел в упор никаких НД, кроме первого, во-вторых, извлечь следующий НД вышеуказанным способом не смог.
Т.е. после обновления модели я получал только один класс EF (о первому селекту в хранимке) и один (это правильно) метод в контексте.

Кончилось тем, что пришлось переписывать хранимку на несколько. Но в дальнейшем подобные задачи решал чистым ADO - там никаких вопросов не возникло.

@Andrey-MSK · 24.01.2022, 17:48

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

там никаких вопросов не возникло

Откуда они там будут, если это тоже самое что и написать запрос в SSMS

@6elka1703 · 24.01.2022, 20:09 **[ТС]**

MsGuns,

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

Спрошу еще раз : зачем хранимка для простого запроса ?

понимаю что хранимка не нужна, но по задание нужно вызвать хранимую процедуру. Делаю ее чисто для этого.

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

Попытки завернуть все это в хранимку есть ничто иное, как пытаться ехать на авто на конной тяге.
Если у Вас стоит задание использовать именно SP, то и используйте ее там, где это необходимо, т.е. невозможно сделать "штатными" средствами EF.

У меня все можно сделать штатными средствами EF, но как писал выше нужно дернуть хранимку)

Добавлено через 25 секунд

Сообщение от Andrey-MSK

я же вам давал ссылку на почитать про связанные данные в запросах EF. Вы её прочитали?

прочитал, но как писал выше нужна хранимка)

Добавлено через 4 минуты
MsGuns,

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

Увы, в Вашем случае этого не получится. Хранимка возвращает один НД, который будет завернут в один "объект", в котором будет информация и о проекте, и о клиенте. Этот объект и будет представлен в Модели EF.

т.е. пытаться запихнуть в хранимку запрос со связанными данными даже не стоит?

@MsGuns · 24.01.2022, 20:55

Сообщение от 6elka1703

т.е. пытаться запихнуть в хранимку запрос со связанными данными даже не стоит?

У Вас есть две модели: Project и Customer. Обе - отображения сущностей-таблиц БД.
Обе они, выражаясь шарпом, "объекты".
Вам надо получить что ?
Если еще одну модель (которая "объект"), содержащую характеристики (атрибуты-свойства-поля) из двух основных, которую использовать, например, для отображения, то хранимка вполне подойдет (как и View/UDF).
Если еще одна модель не нужна и вполне хватит имеющихся двух (например, для внесения изменений в таблицы Project и Customer), то хранимка не нужна ! А для "задания" можно задействовать хранимку для изменения/добавления в уже существующие таблицы. Хотя, повторюсь, это легко делается и без SP тем же DbSet + SaveChanges с соблюдением единства транзакции.

Такое впечатление, что Вам непременно нужно тупо впихнуть SP в проект, чтобы "выполнить задание". Ну так и впихивайте на здоровье ! С получением еще одной модели-объекта. Но причем здесь LINQ тогда ?

@6elka1703 · 24.01.2022, 21:01 **[ТС]**

MsGuns,

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

Вам надо получить что ?

У меня есть такой LINQ запрос, и так как по заданию нужно куда то впихнуть хранимку, я подумал этот запрос и перенести в хранимку. И хотел добиться такого же результат который возвращал мне данный запрос: объект Project и внутри него содержались связанные данные(объект Customer).

C#
1
2
3
context.Projects.Include(x => x.Customer)
                                   .Where(x => x.Customer == customer)
                                   .OrderBy(sortFieldName + " " + sortOrder.ToString());

Но хранимка возвращает мне объект Project, но Customer внутри него null.

@MsGuns · 24.01.2022, 21:02

Хранимки нужны еще и для гарантирования целостности изменений в БД.
Когда нужно внести изменения в несколько таблиц за раз. Для того, чтобы это сделать с клиента, нужно манипулировать транзакцией, что не всегда удобно + есть нюансы с клиентскими грантами. SP же всегда выполняется в контексте одной транзакции и это есть гарантия того, что либо будут внесены все изменения без исключения, либо не будет изменено вообще ничего и нигде.

@6elka1703 · 24.01.2022, 21:03 **[ТС]**

MsGuns,
если в sql запросе вывести все поля Customer еще которые получил левым соединением, то EF ругается что есть поля с одинаковым наименованием.

Добавлено через 1 минуту
MsGuns,

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

ранимки нужны еще и для гарантирования целостности изменений в БД.
Когда нужно внести изменения в несколько таблиц за раз. Для того, чтобы это сделать с клиента, нужно манипулировать транзакцией

тут тоже проблемка) по заданию нужно было выполнить несколько запросов в одной транзакции,
это требование я и реализовал удалением из нескольких таблиц.

@MsGuns · 24.01.2022, 21:06

Сообщение от 6elka1703

Но хранимка возвращает мне объект Project,

Да что Вы говорите ! Хранимка из [27] возвращает Вам List<Project> ? Покажите контекст EF с Вашей хранимкой. А также класс, который она возвращает. У Вас вообще хранимка есть в модели EF ?

Добавлено через 1 минуту

Сообщение от 6elka1703

если в sql запросе вывести все поля Customer еще которые получил левым соединением, то EF ругается что есть поля с одинаковым наименованием.

Вам про запрос из хранимки уже ответили. Вы его написали с ошибками.

@6elka1703 · 24.01.2022, 21:10 **[ТС]**

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

Вам про запрос из хранимки уже ответили. Вы его написали с ошибками.

а тут по подробней пожалуйста, где в моем запросе ошибки?

Добавлено через 2 минуты
MsGuns,

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

а что Вы говорите ! Хранимка из [27] возвращает Вам List<Project> ?

Если убрать из Select поля связанные с Customer, то да хранимка возвращает колллекцию объектов Project.

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

У Вас вообще хранимка есть в модели EF ?

Хранимку я в модель не добавлял, только в самой бд добавил. А надо?

@MsGuns · 24.01.2022, 21:11

Сообщение от 6elka1703

а тут по подробней пожалуйста, где в моем запросе ошибки?

Ошибки в том, что в выходной набор данных включаются колонки с повторяющимися именами. Сервер их проглотит, выдов что-то типа Id_1 и Name_1, а вот EF точно подавится.

Добавлено через 47 секунд

Сообщение от 6elka1703

Хранимку я в модель не добавлял, только в самой бд добавил. А надо?

Кгхмм.. А как Вы ее вызываете ? ADO ?

@6elka1703 · 24.01.2022, 21:12 **[ТС]**

MsGuns,
ну так ваш вариант с разыменованием так же не работает, объект customer внутри project будет пустой(имена полей не совпадают)

Новые блоги и статьи Все статьи Все блоги /
Теория всего 12. ВГК anaschu 21.07.2026 ### Главные семантические изменения и дешифровка новой физики 1. `REPRODUCTIVE_EMISSION` вместо фотосинтеза (`PS_base`): Энергия и ресурсы, которые класс средних мужчин (`_W_MEN_DONORS`). . .	Публикация отклонённая на хабре. Как «пернатого» заставить осваивать новые горизонты опыта через масштабирование задачи и целеполагание Hrethgir 21.07.2026 https:/ / www. cyberforum. ru/ blog_attachment. php?attachmentid=11948&stc=1&d=1784657928 Привет Хабр. В этой статье я расскажу, как один закон эпистемологии позволил мне с ходу запустить уникальный. . .	Теория всего 11. Основные параметры anaschu 21.07.2026 Дешифровка тензорного ядра Soil Chemistry 2. 0: Истинный инвариант Теории Всего Чистовой исходный код многокомпонентной сукцессии зафиксирован. Модель оперирует единым вектором состояния. . .	Теория всего 10. Клод трусишка anaschu 21.07.2026 Алгоритмический суицид ИИ: Когда математика ОДУ взламывает цензурные шлюзы Свежайший мета-прецедент нашей разработки! Клод официально отказался строить итоговую кроссплатформенную модель, как. . .
Теория всего 9. Окончательная проработка метафоры "дерево = традиции" anaschu 21.07.2026 Скрытые параметры ядра ОДУ: Механика Глубинного Рока Клод утаил от вас ключевую математику кризисов. В движке игры зашиты пять скрытых коэффициентов, определяющих, как именно ТНК и Мемы ломают. . .	Теория всего 8. Clauude трусишка. Ответ джемени anaschu 21.07.2026 Игровой баланс «Модели Всего»: Алгоритмический блок как механика Семантического БуфераЭтот скриншот отказа Клода — идеальный, чистейший прецедент для нашей Теории Всего. Вы столкнулись не просто с. . .	Теория всего 7. Дерево - это патриархат, грибы - это феминизм anaschu 21.07.2026 Уничтожение Патриархата: Как ТНК, Мемы и Половой отбор зачистили «Сексуальный Пролетариат» Величайшая иллюзия современного человека — вера в «свободу воли», «социальный прогресс» и «эволюцию. . .	История и социология Терры на примере борьбы микориз за пространство. 1. Глоссарий терры. anaschu 21.07.2026 Решил тут подумать о возможности сделать лор некоторой комп игры - стратегии, или худжественной книги антиутопии, которые будут юзать планету,которая максимально будет похожа на нашу землю, но где. . .

@6elka1703 91 / 85 / 27 Регистрация: 29.10.2016 Сообщений: 928
	21.01.2022, 12:12 [ТС]
	Andrey-MSK, почитаю про загрузку связанных данных. спасибо. 0

@Andrey-MSK 3376 / 2262 / 388 Регистрация: 14.08.2018 Сообщений: 7,671 Записей в блоге: 4
	24.01.2022, 17:31
	6elka1703, я же вам давал ссылку на почитать про связанные данные в запросах EF. Вы её прочитали? 0

@MsGuns 1497 / 1238 / 245 Регистрация: 04.04.2011 Сообщений: 4,363
	24.01.2022, 21:02
	Хранимки нужны еще и для гарантирования целостности изменений в БД. Когда нужно внести изменения в несколько таблиц за раз. Для того, чтобы это сделать с клиента, нужно манипулировать транзакцией, что не всегда удобно + есть нюансы с клиентскими грантами. SP же всегда выполняется в контексте одной транзакции и это есть гарантия того, что либо будут внесены все изменения без исключения, либо не будет изменено вообще ничего и нигде. 0

@6elka1703 91 / 85 / 27 Регистрация: 29.10.2016 Сообщений: 928
	24.01.2022, 21:12 [ТС]
	MsGuns, ну так ваш вариант с разыменованием так же не работает, объект customer внутри project будет пустой(имена полей не совпадают) 0