SQL to LINQ (Entity Framework Core)

Question

I am trying to write SQL to LINQ. I tried Linqpad, but this feature is not available (only LINQ to SQL conversion).

I have this SQL query

 SELECT
    count(*),
    count(TireWidth),
    count(TireRimDiameter),
    count(TireSeason),
    count(PriceCzk)
    FROM(
        SELECT 
            CASE WHEN TireWidth = 255 THEN 1 END TireWidth,
            CASE WHEN TireRimDiameter = 16 AND TireWidth = 255 THEN 1 END TireRimDiameter,
                CASE WHEN TireSeason = 1 THEN 1 END TireSeason,
            CASE WHEN PriceCzk > 50000 THEN 1 END PriceCzk
        FROM Products
        )
    Products

How can I write this query in LINQ? I am using Entity Framework Core, .NET Core 5.

Thank you advance

Svyatoslav Danyliv · Accepted Answer · 2021-02-11 10:30:56Z

1

Trick is in using group by by constant. EF will create just plain aggregation query.

var query = 
    from p in ctx.Products
    group p by 1 into g
    select new
    {
        Count = g.Count(),
        TireWidth = g.Sum(x => x.TireWidth == 255 ? 1 : 0),
        TireRimDiameter = g.Sum(x => x.TireRimDiameter == 16 && x.TireWidth == 255 ? 1 : 0),
        TireSeason = g.Sum(x => x.TireSeason == 1 ? 1 : 0),
        PriceCzk = g.Sum(x => x.PriceCzk > 50000 ? 1 : 0)
    };

answered Feb 11, 2021 at 10:30

Svyatoslav Danyliv

28.3k4 gold badges24 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

NetMage Over a year ago

Note you can also do Where().Count() in some cases (e.g. TireWidth = g.Where(t => t.TireWidth == 255).Count())

Svyatoslav Danyliv Over a year ago

@NetMage, from which EF version?

NetMage Over a year ago

EF Core 5, since the question says EF Core / Net Core 5, I expect that is likely.

Svyatoslav Danyliv Over a year ago

@NetMage, I'll check. Usually it was pain to do that with EF.

NetMage Over a year ago

I tested with EF Core 3.1 and it failed. EF Core 5 translates to a COUNT(CASE in SQL.

|

Adam Gajdečka · Accepted Answer · 2021-03-15 21:06:37Z

I didn't find LINQ solution, but this solution is working and very fast:

 var MySqlParameters = new List<MySqlParameter>();

            MySqlParameters.Add(new MySqlParameter("p_date", DateTime.UtcNow.Date));
            MySqlParameters.Add(new MySqlParameter("p_product_type", (int)(parameters.ProductType ?? 0)));
            MySqlParameters.AddRange(BuildInParameters("p_condition",
                parameters.Conditions.Select(c => (int)c), out var conditionParameters));
            MySqlParameters.AddRange(BuildInParameters("p_brand",
                parameters.Brands.Select(c => (int)c), out var brandParameters));
            MySqlParameters.AddRange(BuildInParameters("p_seller",
                parameters.UserTypes.Select(c => (int)c), out var sellerParameters));

            if (parameters.ToMileage != null)
                MySqlParameters.Add(new MySqlParameter("p_mileage", parameters.ToMileage));
            if (parameters.FromPrice != null)
                MySqlParameters.Add(new MySqlParameter("p_from_price", parameters.FromPrice));
            if (parameters.ToPrice != null)
                MySqlParameters.Add(new MySqlParameter("p_to_price", parameters.ToPrice));
            if (parameters.TireSeason != null)
                MySqlParameters.Add(new MySqlParameter("p_tire_ses", parameters.TireSeason));
            if (parameters.TireVehicleType != null)
                MySqlParameters.Add(new MySqlParameter("p_veh_type", parameters.TireVehicleType));
            if (parameters.TireWidth != null)
                MySqlParameters.Add(new MySqlParameter("p_tire_wid", parameters.TireWidth));
            if (parameters.TireHeight != null)
                MySqlParameters.Add(new MySqlParameter("p_tire_hei", parameters.TireHeight));
            if (parameters.TireRimDiameter != null)
                MySqlParameters.Add(new MySqlParameter("p_tire_rim", parameters.TireRimDiameter));
            if (parameters.TireLoadIndex != null)
                MySqlParameters.Add(new MySqlParameter("p_tire_loa", parameters.TireLoadIndex));
            if (parameters.TireSpeedRating != null)
                MySqlParameters.Add(new MySqlParameter("p_tire_spd", parameters.TireSpeedRating));
            if (parameters.FromYear != null)
                MySqlParameters.Add(new MySqlParameter("p_car_fromYear", parameters.FromYear));
            if (parameters.ToYear != null)
                MySqlParameters.Add(new MySqlParameter("p_car_toYear", parameters.ToYear));

            var builder = new StringBuilder();

                    if (isTireWidth)
                    {
                        builder.AppendLine($@"UNION ALL
    SELECT 'TireWidth' FilterName, TireWidth FilterValue, COUNT(1) as FilterCount FROM
    `products` P LEFT OUTER JOIN `aspnetusers` U ON P.UserId=U.Id 
     WHERE P.Deleted=0 AND TireWidth IS NOT NULL
     AND (P.ExpirationDateUtc='0001-01-01' OR P.ExpirationDateUtc >= @p_date)
     AND P.ProductType = @p_product_type");
                        if (conditionParameters != "") builder.AppendLine($"AND P.ProductCondition IN ({conditionParameters})");
                        if (brandParameters != "") builder.AppendLine($"AND P.Brand IN ({brandParameters})");
                        if (sellerParameters != "") builder.AppendLine($"AND U.Type IN ({sellerParameters})");
                        if (parameters.ToMileage != null) builder.AppendLine($"AND P.Mileage <= @p_mileage");
                        if (parameters.FromPrice != null) builder.AppendLine($"AND P.PriceCzk >= @p_from_price");
                        if (parameters.ToPrice != null) builder.AppendLine($"AND P.PriceCzk <= @p_to_price");
                        if (parameters.TireSeason != null) builder.AppendLine($"AND P.TireSeason = @p_tire_ses");
                        if (parameters.TireVehicleType != null) builder.AppendLine($"AND P.TireVehicleType = @p_veh_type");
                        //if (parameters.TireWidth != null) builder.AppendLine($"AND P.TireWidth = @p_tire_wid");
                        if (parameters.TireHeight != null) builder.AppendLine($"AND P.TireHeight = @p_tire_hei");
                        if (parameters.TireRimDiameter != null) builder.AppendLine($"AND P.TireRimDiameter = @p_tire_rim");
                        if (parameters.TireLoadIndex != null) builder.AppendLine($"AND P.TireLoadIndex = @p_tire_loa");
                        if (parameters.TireSpeedRating != null) builder.AppendLine($"AND P.TireSpeedRating = @p_tire_spd");
                        builder.AppendLine("GROUP BY TireWidth");
                    }
    
                    if (isTireVehicleType)
                    {
                        builder.AppendLine($@"UNION ALL
    SELECT 'TireVehicleType' FilterName, TireVehicleType FilterValue, COUNT(1) as FilterCount FROM
    `products` P LEFT OUTER JOIN `aspnetusers` U ON P.UserId=U.Id 
     WHERE P.Deleted=0 AND TireVehicleType IS NOT NULL
     AND (P.ExpirationDateUtc='0001-01-01' OR P.ExpirationDateUtc >= @p_date)
     AND P.ProductType = @p_product_type");
                        if (conditionParameters != "") builder.AppendLine($"AND P.ProductCondition IN ({conditionParameters})");
                        if (brandParameters != "") builder.AppendLine($"AND P.Brand IN ({brandParameters})");
                        if (sellerParameters != "") builder.AppendLine($"AND U.Type IN ({sellerParameters})");
                        if (parameters.ToMileage != null) builder.AppendLine($"AND P.Mileage <= @p_mileage");
                        if (parameters.FromPrice != null) builder.AppendLine($"AND P.PriceCzk >= @p_from_price");
                        if (parameters.ToPrice != null) builder.AppendLine($"AND P.PriceCzk <= @p_to_price");
                        if (parameters.TireSeason != null) builder.AppendLine($"AND P.TireSeason = @p_tire_ses");
                        //if (parameters.TireVehicleType != null) builder.AppendLine($"AND P.TireVehicleType = @p_veh_type");
                        if (parameters.TireWidth != null) builder.AppendLine($"AND P.TireWidth = @p_tire_wid");
                        if (parameters.TireHeight != null) builder.AppendLine($"AND P.TireHeight = @p_tire_hei");
                        if (parameters.TireRimDiameter != null) builder.AppendLine($"AND P.TireRimDiameter = @p_tire_rim");
                        if (parameters.TireLoadIndex != null) builder.AppendLine($"AND P.TireLoadIndex = @p_tire_loa");
                        if (parameters.TireSpeedRating != null) builder.AppendLine($"AND P.TireSpeedRating = @p_tire_spd");
                        builder.AppendLine("GROUP BY TireVehicleType");
                    }

   var result = _context.FilterQueries.FromSqlRaw(builder.ToString(), MySqlParameters.ToArray());
            return result;

Collectives™ on Stack Overflow

SQL to LINQ (Entity Framework Core)

2 Answers 2

11 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related