Convert C# Entity Framework linq query to SQL-CodePudding

I am converting the C# linq query to SQL, LINQ never return the values.

But the same query I wrote in SQL returns some values. Can anyone help me find out what the issue is for my SQL query?

LINQ query:

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 && JsonExtensions.JsonValue(lact.ActivationInfoJSON, "$.SubscriptionKey") !=
                    " " && (JsonExtensions.JsonValue(lact.ActivationInfoJSON, "$.IsConverted") == null || JsonExtensions.JsonValue(lact.ActivationInfoJSON, "$.IsConverted") == "0" || JsonExtensions.JsonValue(lact.ActivationInfoJSON, "$.IsConverted") == "false") && 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 query:

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'

CodePudding user response：

To start from a valid point, execute the code where it fires this linq query and use sql profiler to catch it up. That is a good way to find the exact equivalent sql statement it finally produces and executes. You need to set up a trace to sql profiler prior to execute the linq. Get the statement and then you can compare with the sql you already have.

this sql:

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

is not equal to:

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

as in first case you compare to database NULL value using '=' and this requires ansi_nulls off to work properly and it is not a good practice.

CodePudding user response：

ORMs like EF Core are meant to Map Objects to Relational constructs. Instead of trying to write SQL in C# through LINQ, you should try to Map the attributes you need to entity properties.

In this case the SubscriptionKey and IsConverted fields should appear in the table itself, either as proper fields or computed columns. If that's not possible, you could use computed columns to map the SubscriptionKey and IsConverted attributes to entity properties so you can use them in queries.

In your LicenseActivation class add these properties:

    public bool? IsConverted {get;set;}

    public string? SubscriptionKey {get;set;}

    public string? CustomerEmail {get;set;}

In your DbContext, you can specify computed columns with 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')");

}

This will allow you to use the properties in LINQ queries.

You shouldn't have to use all those JOINs either. It's the ORM's job to generate JOINs from the relations between objects. If you add proper relations between the entities the query could be simplified to :

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

or, if the AccountId fields don't hold the same data :

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

EF Core will generate the appropriate SQL and JOIN clauses to get from LicenseActivation to License based on the relations between the entities