#c# #sql-server #linq
#c# #sql-сервер #linq
Вопрос:
Я новичок в выражениях linq и пытаюсь разработать эквивалентную версию linq этого tsql:
SELECT
p.Name,
Count(a.Street) [Number of addresses],
c.ContractType,
COUNT(cv.ContractVersionId) Number,
MAX(cv.ChangedDate) [Last change date]
From Person p
LEFT JOIN Address a ON p.PersonId = a.PersonId AND a.ChangedDate IS NULL
LEFT JOIN Contract c ON p.PersonId = c.PersonId
LEFT JOIN ContractVersion cv ON c.ContractId = cv.ContractId
GROUP BY p.Name, c.ContractType
Это приводит к выводу:
Name Number of addresses ContractType Number Last change date
Bob 1 NULL 0 NULL
Allice 1 Buy 1 2020-10-31 00:00:00.000
Karen 0 Buy 3 2020-09-01 00:00:00.000
Peter 3 Lease 3 2020-07-10 00:00:00.000
Tom 5 Lease 5 2020-09-14 00:00:00.000
Allice 3 Rent 3 2020-05-10 00:00:00.000
Данные для воспроизведения:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Person]') AND type in (N'U'))
DROP TABLE [dbo].Person
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Address]') AND type in (N'U'))
DROP TABLE [dbo].Address
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Contract]') AND type in (N'U'))
DROP TABLE [dbo].Contract
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ContractVersion]') AND type in (N'U'))
DROP TABLE [dbo].ContractVersion
GO
CREATE TABLE Person (
PersonId Uniqueidentifier NOT NULL,
Name NVARCHAR(100) NOT NULL
)
CREATE TABLE Address(
AddressId Uniqueidentifier NOT NULL,
PersonId Uniqueidentifier NOT NULL,
Street NVARCHAR(100) NOT NULL,
ChangedDate DATETIME NULL
)
CREATE TABLE Contract (
ContractId Uniqueidentifier NOT NULL,
PersonId Uniqueidentifier NOT NULL,
ContractType NVARCHAR(100) NOT NULL
)
CREATE TABLE ContractVersion (
ContractVersionId Uniqueidentifier NOT NULL,
ContractId Uniqueidentifier NOT NULL,
ContractText NVARCHAR(100) NOT NULL,
ChangedDate DATETIME NULL
)
INSERT INTO Person
VALUES
('{B8A97537-AF09-45FD-A723-1BF43796DADA}','Tom'),
('{124A34C0-1AB3-4449-AEE2-04ED2732B8ED}','Allice'),
('{E501CC32-C587-43C7-B0DD-096F04EE80AA}','Peter'),
('{9D1E37BE-D032-45CC-AA1D-06ECBB9C59EA}','Karen'),
('{049B5985-F4D0-448C-9391-8E08495DF61F}','Bob')
INSERT INTO Address
VALUES
('{AF3BE020-6851-46E1-9D04-9959C665A80D}','{B8A97537-AF09-45FD-A723-1BF43796DADA}','Last address 1',NULL),
('{A0957F82-8922-452C-AA1B-4EA05A1B121B}','{124A34C0-1AB3-4449-AEE2-04ED2732B8ED}','Last address 2',NULL),
('{10D60EDC-75B3-412F-AA0B-962746EB72BD}','{E501CC32-C587-43C7-B0DD-096F04EE80AA}','Last address 3',NULL),
('{93A10053-5059-4127-8EA1-C0DF77760E84}','{049B5985-F4D0-448C-9391-8E08495DF61F}','Last address 4',NULL),
('{F1E838FE-7074-4AB3-8A4D-0895CEB98362}','{B8A97537-AF09-45FD-A723-1BF43796DADA}','D','2020-01-01'),
('{4CB0D80D-7F74-4755-9C1A-08A03EE88B4B}','{124A34C0-1AB3-4449-AEE2-04ED2732B8ED}','D','2020-01-01'),
('{B42DE51A-3EAD-4AAC-B5DC-75493FE214E5}','{E501CC32-C587-43C7-B0DD-096F04EE80AA}','D','2020-01-01')
INSERT INTO Contract
VALUES
('{64989F02-793E-4313-9324-4916D6C5D610}','{B8A97537-AF09-45FD-A723-1BF43796DADA}','Lease'),
('{0101F8CD-E72E-4D9B-A8BD-A94BBDB39740}','{124A34C0-1AB3-4449-AEE2-04ED2732B8ED}','Rent'),
('{D1F94A9C-0718-4E18-B63F-B506C5E2C70E}','{E501CC32-C587-43C7-B0DD-096F04EE80AA}','Lease'),
('{A8397E6D-597A-4024-91DC-98EA0D9FF848}','{9D1E37BE-D032-45CC-AA1D-06ECBB9C59EA}','Buy'),
('{D47E82B3-2D41-4CCB-BD6F-003BD9C49BEA}','{B8A97537-AF09-45FD-A723-1BF43796DADA}','Lease'),
('{5EC4E588-00A5-442B-BD7B-0C9FD27E8076}','{124A34C0-1AB3-4449-AEE2-04ED2732B8ED}','Buy')
INSERT INTO ContractVersion
VALUES
('{E3E26DE5-F00A-4BEB-9A48-72AE9EA29F2E}','{64989F02-793E-4313-9324-4916D6C5D610}','Created','2020-01-01'),
('{C5EFAB2E-EE41-4A10-A708-44CA19E505A9}','{0101F8CD-E72E-4D9B-A8BD-A94BBDB39740}','Created','2020-05-01'),
('{43EDFE20-B1EF-4236-8D4E-478F445EC327}','{D1F94A9C-0718-4E18-B63F-B506C5E2C70E}','Created','2020-06-03'),
('{16870733-0EC3-497B-BA51-770C921A245A}','{A8397E6D-597A-4024-91DC-98EA0D9FF848}','Created','2020-08-15'),
('{93CAEA22-D796-4237-A706-47C3D6AE8700}','{D47E82B3-2D41-4CCB-BD6F-003BD9C49BEA}','Created','2020-09-12'),
('{9D003A79-1376-4512-922F-88A33EC72CCB}','{5EC4E588-00A5-442B-BD7B-0C9FD27E8076}','Created','2020-10-31'),
('{8BD9E556-BB56-4404-A7C2-C6C06D321127}','{64989F02-793E-4313-9324-4916D6C5D610}','Reviewed','2020-01-05'),
('{E46D1C7E-94BF-43BC-883D-7960CE719C07}','{0101F8CD-E72E-4D9B-A8BD-A94BBDB39740}','Reviewed','2020-05-01'),
('{0B5B594C-78BF-49B7-834B-3E9A617A16F9}','{D1F94A9C-0718-4E18-B63F-B506C5E2C70E}','Reviewed','2020-06-08'),
('{931C3910-DFD6-4EB4-9826-9518B3C6BC54}','{A8397E6D-597A-4024-91DC-98EA0D9FF848}','Reviewed','2020-08-16'),
('{6F97D07E-9AAD-488E-87D3-D2A886547B1C}','{D47E82B3-2D41-4CCB-BD6F-003BD9C49BEA}','Reviewed','2020-09-14'),
('{BD3B3413-8853-4199-BC93-B23D49F77A9C}','{64989F02-793E-4313-9324-4916D6C5D610}','Finalized','2020-04-01'),
('{5DA560DE-69D7-4244-A320-AF3FE4135B22}','{0101F8CD-E72E-4D9B-A8BD-A94BBDB39740}','Finalized','2020-05-10'),
('{8392D528-438C-4F82-8BAC-69C1913CF610}','{D1F94A9C-0718-4E18-B63F-B506C5E2C70E}','Finalized','2020-07-10'),
('{0DC060B3-12F0-4927-8526-57E87C0150EA}','{A8397E6D-597A-4024-91DC-98EA0D9FF848}','Finalized','2020-09-01')
SELECT
p.Name,
Count(a.Street) [Number of addresses],
c.ContractType,
COUNT(cv.ContractVersionId) Number,
MAX(cv.ChangedDate) [Last change date]
From Person p
LEFT JOIN Address a ON p.PersonId = a.PersonId AND a.ChangedDate IS NULL
LEFT JOIN Contract c ON p.PersonId = c.PersonId
LEFT JOIN ContractVersion cv ON c.ContractId = cv.ContractId
GROUP BY p.Name, c.ContractType
Вся помощь приветствуется!
Обновление: я могу создать это выражение linq, но шрифт не понимает, как мне следует группировать, поскольку данные находятся в разных таблицах.
from p in Persons
join a in Addresses on new { p.PersonId} equals new { a.PersonId} into addressGroup from a in addressGroup.DefaultIfEmpty()
join c in Contracts on p.PersonId equals c.PersonId into contractGroups from c in contractGroups.DefaultIfEmpty()
join cv in ContractVersions on c.ContractId equals cv.ContractId
select new {p.Name, a.Street, c.ContractType, cv.ChangedDate}
ОБНОВЛЕНИЕ 2:
Я попробовал запрос @jdweng ниже, и полученный sql оказался таким:
SELECT [t5].[Name] AS [name], (
SELECT COUNT(*)
FROM (
SELECT NULL AS [EMPTY]
FROM (
SELECT [t7].[Street] AS [value], [t6].[Name], [t8].[ContractType]
FROM [Person] AS [t6]
LEFT OUTER JOIN [Address] AS [t7] ON [t6].[PersonId] = [t7].[PersonId]
LEFT OUTER JOIN [Contract] AS [t8] ON [t6].[PersonId] = [t8].[PersonId]
INNER JOIN [ContractVersion] AS [t9] ON [t8].[ContractId] = [t9].[ContractId]
) AS [t10]
WHERE ([t5].[Name] = [t10].[Name]) AND ((([t5].[value] IS NULL) AND ([t10].[ContractType] IS NULL)) OR (([t5].[value] IS NOT NULL) AND ([t10].[ContractType] IS NOT NULL) AND ((([t5].[value] IS NULL) AND ([t10].[ContractType] IS NULL)) OR (([t5].[value] IS NOT NULL) AND ([t10].[ContractType] IS NOT NULL) AND ([t5].[value] = [t10].[ContractType])))))
GROUP BY [t10].[value]
) AS [t11]
) AS [numberOfAddresses], [t5].[value] AS [contractType], (
SELECT COUNT(*)
FROM (
SELECT NULL AS [EMPTY]
FROM [Person] AS [t12]
LEFT OUTER JOIN [Address] AS [t13] ON [t12].[PersonId] = [t13].[PersonId]
LEFT OUTER JOIN [Contract] AS [t14] ON [t12].[PersonId] = [t14].[PersonId]
INNER JOIN [ContractVersion] AS [t15] ON [t14].[ContractId] = [t15].[ContractId]
WHERE ([t5].[Name] = [t12].[Name]) AND ((([t5].[value] IS NULL) AND ([t14].[ContractType] IS NULL)) OR (([t5].[value] IS NOT NULL) AND ([t14].[ContractType] IS NOT NULL) AND ((([t5].[value] IS NULL) AND ([t14].[ContractType] IS NULL)) OR (([t5].[value] IS NOT NULL) AND ([t14].[ContractType] IS NOT NULL) AND ([t5].[value] = [t14].[ContractType])))))
GROUP BY [t15].[ContractVersionId]
) AS [t16]
) AS [number]
FROM (
SELECT [t4].[Name], [t4].[value]
FROM (
SELECT [t0].[Name], [t2].[ContractType] AS [value]
FROM [Person] AS [t0]
LEFT OUTER JOIN [Address] AS [t1] ON [t0].[PersonId] = [t1].[PersonId]
LEFT OUTER JOIN [Contract] AS [t2] ON [t0].[PersonId] = [t2].[PersonId]
INNER JOIN [ContractVersion] AS [t3] ON [t2].[ContractId] = [t3].[ContractId]
) AS [t4]
GROUP BY [t4].[Name], [t4].[value]
) AS [t5]
Он почти дает желаемый результат и занимает в 20 раз больше времени, чем исходный tsql.
Комментарии:
1. Используете ли вы
entity framework? Если да, то какая версия? и также делитесь соответствующимModels.2. Прямо сейчас я работаю только в Linqpad непосредственно с приведенными выше таблицами с выражениями linq, поэтому для них нет EFcontexts или моделей.
3. Существует, он генерируется Linqpad. Взгляните на свойства классов, особенно свойства навигации, и сделайте несколько первых попыток. На самом деле здесь нет вопроса. Вы говорите только то, что хотите.
4. @GertArnold, я обновил негруппированное выражение linq, но я не могу понять, как группировать данные
5. Как это часто бывает, вы атакуете это с учетом SQL и сразу вводите joins . Начните с разработки правильной модели базы данных с первичными и внешними ключами. Затем Linq-to-sql (который использует Linqpad) сгенерирует модель со свойствами навигации. Если вы дошли до этого момента, попробуйте написать несколько запросов, используя эти свойства, и посмотрите, что они делают. Тогда вы увидите, что можете просто делать такие вещи, как
p.Addresses.Count().
Ответ №1:
Не уверен, как ядро EF поддерживает Count условие with, поэтому просто эмулируйте с Sum помощью .
var query =
from p in Persons
join a in Addresses on p.PersonId equals a.PersonId into addressGroup
from a in addressGroup.DefaultIfEmpty()
join c in Contracts on p.PersonId equals c.PersonId into contractGroups
from c in contractGroups.DefaultIfEmpty()
join cv in ContractVersions on c.ContractId equals cv.ContractId in versionsGroup
from cv in versionsGroup.DefaultIfEmpty()
group new { a, cv } by new { p.Name, c.ContractType } into g
select new
{
g.Key.Name,
NumberOfAddresses = g.Sum(x => x.a.Street != null ? 1 : 0),
g.Key.ContractType,
Number = g.Sum(x => x.cv.ContractVersionId != null ? 1 : 0),
LastChangeDate = g.Max(x => x.cv.ChangedDate)
}
Комментарии:
1. Не думаю, что вам нужно левое внешнее соединение.
2. Сгенерированный @jdweng SQL должен быть равен вопросу.
3. @SvyatoslavDanyliv, это приводит к точно такому же плану выполнения! Спасибо!
4. @SvyatoslavDanyliv : Нет!!! Язык SQL очень ограничен и часто требует сложных запросов, когда они не нужны в эквивалентном c # linq.
5. @jdweng, ну и что? Этот запрос легко преобразуется с помощью LINQ. И я думаю, что ядро EF очень ограничено для создания необходимого SQL, что важно, если вы не хотите передавать всю таблицу клиенту и выполнять постобработку.
Ответ №2:
Попробуйте выполнить следующее
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
Context db = new Context();
var results = (from p in db.Person
join a in db.Address on p.PersonId equals a.PersonId
join c in db.Contract on p.PersonId equals c.PersonId
join cv in db.ContractVersion on c.ContractId equals cv.ContractId
select new { p = p, a = a, c = c, cv = cv}
).GroupBy(x => new { name = x.p.Name, contractType = x.c.ContractType })
.Select(x => new {
name = x.Key.name,
numberOfAddresses = x.GroupBy(y => y.a.Street).Count(),
contractType = x.Key.contractType,
number = x.GroupBy(y => y.cv.ContractVersionId).Count(),
lastChangeDate = x.OrderByDescending(y => y.cv.ChangedDate).FirstOrDefault().cv.ChangedDate
})
.ToList();
}
}
public class Context
{
public List<Person> Person { get; set; }
public List<Address> Address { get; set; }
public List<Contract> Contract { get; set; }
public List<ContractVersion> ContractVersion { get; set; }
}
public class Person
{
public string Name { get; set; }
public string PersonId { get; set; }
}
public class Address
{
public string PersonId { get; set; }
public string Street { get; set; }
public DateTime ChangedDate { get; set; }
}
public class Contract
{
public string PersonId { get; set; }
public string ContractType { get; set; }
public string ContractId { get; set; }
}
public class ContractVersion
{
public string ContractId { get; set; }
public DateTime ChangedDate { get; set; }
public string ContractVersionId { get; set; }
}
}
Вот 2-й запрос, который может выполняться быстрее :
var results = db.Person.Select(p => new {
Person = p,
Address = db.Address.Where(a => p.PersonId == a.PersonId),
Contract = db.Contract.Where(c => p.PersonId == c.PersonId),
ContractVersion = db.ContractVersion.Where(cv => p.PersonId == cv.ContractId)
}).GroupBy(x => new { name = x.Person.Name, contractType = x.Contract.FirstOrDefault().ContractType})
.Select(x => new
{
name = x.Key.name,
numberOfAddresses = x.Select(y => y.Address.SelectMany(z => z.Street)).Count(),
contractType = x.Key.contractType,
number = x.GroupBy(y => y.ContractVersion.SelectMany(z => z.ContractVersionId)).Count(),
lastChangeDate = x.SelectMany(y => y.ContractVersion.Select(z => z.ChangedDate)).OrderByDescending(y => y).FirstOrDefault()
})
.ToList();
Комментарии:
1. Спасибо! Я получаю «Значение не может быть нулевым. (Параметр ‘outer’)» в операторе результатов var. Это потому, что contracttype равен null для одного человека?
2. Это может быть дата или contractType . Вы можете прокомментировать, чтобы узнать, какой из них. Это, скорее всего, дата, поскольку дата не может быть нулевой.
3. Это дает несколько близкий результат, но TSQL, который получается из него, совсем не хорош. Это примерно в 20 раз медленнее, чем SQL, который я пытаюсь имитировать. (вы можете увидеть запрос в моем обновлении 2). Есть ли способ создать промежуточные группы и объединить их в новые таблицы?
4. Выполняется ли запрос медленно также с использованием SQL Server Management Studio? Необходимо выяснить, вызвано ли медленное время интерфейсом c # / sql или фактическим временем выполнения запроса. Похоже, что вместо объединения может потребоваться использовать WHERE .
5. Да, в реальном примере они работают очень медленно, потому что результат выглядит так: «Для каждой строки сделайте еще один выбор в другую таблицу, в которой совпадают идентификаторы», что означает, что запрос со 100 строками в первой таблице приведет к 101 «запросам» к базе данных