Блог

Главная - Вопросы по программированию - Преобразование запроса C# Entity Framework linq в SQL

Преобразование запроса C# Entity Framework linq в SQL

#c# #sql-server

Вопрос:

Я преобразую запрос C# linq в SQL, LINQ никогда не возвращает значения.

Но тот же запрос, который я написал в SQL, возвращает некоторые значения. Может ли кто-нибудь помочь мне выяснить, в чем проблема с моим SQL-запросом?

Запрос LINQ:

 var query = from l in _DbContext.Licenses
            join lp in _DbContext.LicenseParts on l.PartNumber equals lp.PartNumber
            join lpc in _DbContext.LicensePartConfigurations on lp.Id equals lpc.LicensePartId
            join p in _DbContext.Products on lp.ProductId equals p.Id
            join lsn in _DbContext.LicenseSerialNumbers on l.Id equals lsn.LicenseId
            join lact in _DbContext.LicenseActivations on new { a = lsn.Id, b = lp.ProductId } equals new { a = lact.LicenseSerialNumberId, b = lact.ProductId }
            where lact.AccountId == AccountId amp;amp; JsonExtensions.JsonValue(lact.ActivationInfoJSON, "$.SubscriptionKey") !=
                    " " amp;amp; (JsonExtensions.JsonValue(lact.ActivationInfoJSON, "$.IsConverted") == null || JsonExtensions.JsonValue(lact.ActivationInfoJSON, "$.IsConverted") == "0" || JsonExtensions.JsonValue(lact.ActivationInfoJSON, "$.IsConverted") == "false") amp;amp; p.Name == "ClearPass Legacy"
            select new SubscriptionKeys { SubscriptionKey = JsonExtensions.JsonValue(lact.ActivationInfoJSON, "$.SubscriptionKey"), CustomerMail = JsonExtensions.JsonValue(lact.ActivationInfoJSON, "$.CustomerMail"), CustomerName = JsonExtensions.JsonValue(lact.ActivationInfoJSON, "$.CustomerName") };

        response.PageSize = pageSize;
        response.PageNumber = pageNumber;
        response.Model = await query.Distinct().ToListAsync();
        response.ItemsCount = response.Model.Count();

SQL-запрос:

 SELECT
    l.AccountId,CustomerMail,
    JSON_VALUE(ActivationInfo, '$.SubscriptionKey') 
FROM
    Licenses l
JOIN
    LicenseParts lp ON l.PartNumber = lp.PartNumber
JOIN
    LicensePartConfigurations lpc ON lp.Id = lpc.LicensePartId
JOIN
    Products p ON lp.ProductId = p.Id
JOIN
    LicenseSerialNumbers lsn ON l.Id = lsn.LicenseId
JOIN
    LicenseActivations lact ON lsn.Id = lact.LicenseSerialNumberId 
                            AND lp.ProductId = lact.ProductId
WHERE
    lact.AccountId = 'QWNjb3VudDoxNTMwNDAzMi00MWM2LTExZTktOWYzMy1kMzQxZjE5OWZlYjM='
    AND JSON_VALUE(lact.ActivationInfo, '$.SubscriptionKey') != ' '
    AND (JSON_VALUE(lact.ActivationInfo, '$.IsConverted') = NULL OR
         JSON_VALUE(lact.ActivationInfo, '$.IsConverted') = 0 OR
         JSON_VALUE(lact.ActivationInfo, '$.IsConverted') = 'false')
    AND p.Name = 'ClearPass Legacy'

Комментарии:

1. EF Core-это ORM, а не замена SQL. Он использует сущности и классы, а не объекты. Вам вообще не нужно использовать объединения, ORM будет генерировать объединения по мере необходимости на основе отношений между сущностями. Вы не можете использовать в LINQ-to-Сущностях что-либо, что не сопоставляется непосредственно с SQL. Это означает, что вы не можете использовать какие-либо функции, если нет прямого сопоставления с SQL. Что бы это ни JsonExtensions было, это не одно из них.

2. Только несколько очень распространенных методов, таких как string.Contains или String.StartsWith , отображаются напрямую. В остальном вам нужно использовать методы в dbфункциях . Функций, связанных с Json, не существует

3.@PanagiotisKanavos Большинство СУБД сегодня поддерживают JSON-in-SQL, так что это вполне JsonExtensions могут быть допустимые [DbFunction] методы. Просто говорю…

4. Вы можете включить ведение журнала и ведение журнала конфиденциальных данных, после чего вы сможете сравнить сгенерированные выходные данные SQL с требуемыми.

5.Не делай этого! JSON_VALUE(lact.ActivationInfo, '$.IsConverted') = NULL — сравнение с NULL в SQL должно выполняться только с помощью IS NULL или IS NOT NULL — сравнение с оператором equal не будет работать !

Ответ №1:

Чтобы начать с допустимой точки, выполните код, в котором он запускает этот запрос linq, и используйте профилировщик sql, чтобы выполнить его. Это хороший способ найти точный эквивалент sql-оператора, который он в конечном итоге создает и выполняет. Вам необходимо настроить трассировку в профилировщик sql перед выполнением linq. Получите инструкцию, а затем вы сможете сравнить ее с уже имеющимся sql.

этот sql:

 (JSON_VALUE(lact.ActivationInfo, '$.IsConverted') = NULL

не равно:

 (JsonExtensions.JsonValue(lact.ActivationInfoJSON, "$.IsConverted") == null

как и в первом случае, вы сравниваете значение NULL с базой данных с помощью»=», и для правильной работы требуется, чтобы ansi_nulls был отключен, и это не очень хорошая практика.

Комментарии:

1. Это неверно. NULL не равно ничему, поэтому условие SQL никогда не будет соответствовать ни NULL одному s. Условие LINQ-to-Entities x.Prop == null будет преобразовано в Prop IS NULL само EF. Нет причин использовать DBNull. Ценность. Который использовался в ADO.NET до того, как были введены типы, допускающие обнуление

2. Панайотис Канавос зависит от параметра ansi_nulls. Это очень хорошо сочетается. Проверьте это сами: создайте таблицу #test_null(id int) вставьте в значения #test_null(1) вставьте в значения #test_null(null) установите значение ansi_null выключено выберите * из #test_null, где id = null установите значение ansi_null на выбор * из #test_null, где id = null отбросьте таблицу #test_null

3. Да, если вы неправильно используете базу данных, вы можете многое сделать. Хотя это скорее ограничивает карьеру. И причина для увольнения. И отличный способ провалить каждый вопрос SQL — интервью. Просто потому, что некоторые системы неправильно использовали нулевые настройки 30 лет назад, не означает, что есть какие-либо причины продолжать это делать. Я не могу не подчеркнуть, насколько плоха эта идея. Это wrapping-a-fuse-in-aluminum-foil довольно плохо

4. Конечно, но это не вопрос ваших или моих предпочтений, отраслевых стандартов или чего — то еще… поскольку опубликованный SQL был «= NULL», указывает, что настройки, вероятно, не являются ansi.

5. На самом деле, это стандарт. = NULL это ошибка, простая и понятная. Такой код обычно встречается в задаваемых вопросах Why can't I find nulls ? Допустим, что говорить it's a preference в субботу SQL, SQL meetup или .NET meetup было бы не очень хорошо.


Ответ №2:

ORM, подобные EF Core, предназначены для сопоставления объектов с реляционными конструкциями. Вместо того, чтобы пытаться писать SQL на C# через LINQ, вам следует попытаться сопоставить необходимые атрибуты со свойствами сущности.

В этом случае поля SubscriptionKey и IsConverted должны отображаться в самой таблице либо как правильные поля, либо как вычисляемые столбцы. Если это невозможно, вы можете использовать вычисляемые столбцы для сопоставления атрибутов SubscriptionKey и IsConverted со свойствами сущностей, чтобы использовать их в запросах.

В свой LicenseActivation класс добавьте эти свойства:

     public bool? IsConverted {get;set;}

    public string? SubscriptionKey {get;set;}

    public string? CustomerEmail {get;set;}

В вашем DbContext вы можете указать вычисляемые столбцы с HasComputedColumnSql :

 protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<LicenseActivation>()
        .Property(b => b.SubscriptionKey)
        .HasComputedColumnSql("JSON_VALUE(ActivationInfo, '$.SubscriptionKey')");

    modelBuilder.Entity< LicenseActivations >()
        .Property(b => b.IsConverted)
        .HasComputedColumnSql("JSON_VALUE(ActivationInfo, '$.IsConverted')");

    modelBuilder.Entity< LicenseActivations >()
        .Property(b => b.CustomerEmail)
        .HasComputedColumnSql("JSON_VALUE(ActivationInfo, '$.CustomerEmail')");

}

Это позволит вам использовать свойства в запросах LINQ.

Вам также не нужно использовать все эти соединения. Задача ORM заключается в создании соединений на основе отношений между объектами. Если вы добавите правильные отношения между сущностями, запрос может быть упрощен до :

 var binValue='QWNjb3VudDoxNTMwNDAzMi00MWM2LTExZTktOWYzMy1kMzQxZjE5OWZlYjM=';
var query=_dbContext.LicenseActivations
                .Where(lact=>
                    lact.AccountId == binValue 
                    amp;amp; (lact.IsConverted==null || !lact.IsConverted))
                .Select(lact=>new { 
                            lact.AccountId, 
                            lact.SubscriptionKey, 
                            lact.CustomerEmail});

или, если AccountId поля не содержат одинаковые данные :

 .Select(lact=>new { 
    AccountId =lact.LicensePart.License.AccountId, 
    lact.SubscriptionKey, 
    lact.CustomerEmail
});

EF Core создаст соответствующие предложения SQL и JOIN для перехода от LicenseActivation к License на основе отношений между сущностями

Комментарии:

1. пользователь просит преобразовать в SQL, а не улучшать код c# linkq

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

3. Ему лучше забрать созданный sql — запрос из профилировщика sql и начать оттуда. Если он изменит код, он может изменить существующую логику. Бессмысленно что-либо менять в c# linq, так как его цель-создать эквивалентный оператор sql.

Метки: