{"id":8120,"date":"2023-11-09T10:26:20","date_gmt":"2023-11-09T09:26:20","guid":{"rendered":"https:\/\/solidt.eu\/site\/?p=8120"},"modified":"2023-12-13T11:06:21","modified_gmt":"2023-12-13T10:06:21","slug":"linq-queryextensions-as-expressions-for-sql","status":"publish","type":"post","link":"https:\/\/solidt.eu\/site\/linq-queryextensions-as-expressions-for-sql\/","title":{"rendered":"Linq QueryExtensions as expressions for SQL"},"content":{"rendered":"\n<div style=\"height: 250px; position:relative; margin-bottom: 50px;\" class=\"wp-block-simple-code-block-ace\"><pre class=\"wp-block-simple-code-block-ace\" style=\"position:absolute;top:0;right:0;bottom:0;left:0\" data-mode=\"csharp\" data-theme=\"monokai\" data-fontsize=\"14\" data-lines=\"Infinity\" data-showlines=\"true\" data-copy=\"false\">using System.Linq.Expressions;\nusing Domain.Forecasts;\n\nnamespace EfAdapter.Extensions\n{\n    public static class QueryExtensions\n    {\n        public static IQueryable&lt;T> Between&lt;T, TKey>(\n            this IQueryable&lt;T> query,\n            Expression&lt;Func&lt;T, TKey>> keySelector,\n            TKey minValue,\n            TKey maxValue)\n            where TKey : IComparable&lt;TKey>\n        {\n            var parameter = keySelector.Parameters.Single();\n            var body = Expression.AndAlso(\n                Expression.GreaterThanOrEqual(keySelector.Body, Expression.Constant(minValue)),\n                Expression.LessThanOrEqual(keySelector.Body, Expression.Constant(maxValue))\n            );\n            var predicate = Expression.Lambda&lt;Func&lt;T, bool>>(body, parameter);\n            return query.Where(predicate);\n        }\n\n        public static IQueryable&lt;T> BetweenYearWeek&lt;T>(\n            this IQueryable&lt;T> query,\n            Expression&lt;Func&lt;T, int>> yearWeekSelector,\n            YearWeek minValue,\n            YearWeek maxValue)\n        {\n            var parameter = yearWeekSelector.Parameters.Single();\n            var body = Expression.AndAlso(\n                Expression.GreaterThanOrEqual(yearWeekSelector.Body, Expression.Constant(minValue.AsInt())),\n                Expression.LessThanOrEqual(yearWeekSelector.Body, Expression.Constant(maxValue.AsInt()))\n            );\n            var predicate = Expression.Lambda&lt;Func&lt;T, bool>>(body, parameter);\n\n            return query.Where(predicate);\n        }\n    }\n    \n    \/\/\/ &lt;summary>\n    \/\/\/ Use as:\n    \/\/\/ .InDateTimeRange(x => x.ValidFrom, x => x.ValidTo, periodFrom, periodTo)\n    \/\/\/ \n    \/\/\/ Is about equal to:\n    \/\/\/ .Where(x => x.ValidFrom == null || x.ValidFrom &lt;= periodFrom)\n    \/\/\/ .Where(x => x.ValidTo == null || x.ValidTo >= periodTo)\n    \/\/\/ &lt;\/summary>\n    \/\/\/ &lt;typeparam name=\"T\">&lt;\/typeparam>\n    \/\/\/ &lt;param name=\"query\">&lt;\/param>\n    \/\/\/ &lt;param name=\"minSelector\">&lt;\/param>\n    \/\/\/ &lt;param name=\"maxSelector\">&lt;\/param>\n    \/\/\/ &lt;param name=\"minValue\">&lt;\/param>\n    \/\/\/ &lt;param name=\"maxValue\">&lt;\/param>\n    \/\/\/ &lt;returns>&lt;\/returns>\n    \/\/\/ &lt;exception cref=\"ArgumentNullException\">&lt;\/exception>\n    public static IQueryable&lt;T> InDateTimeRange&lt;T>(\n        this IQueryable&lt;T> query,\n        Expression&lt;Func&lt;T, DateTime?>> minSelector,\n        Expression&lt;Func&lt;T, DateTime?>> maxSelector,\n        DateTime? minValue,\n        DateTime? maxValue)\n    {\n        if (query == null) throw new ArgumentNullException(nameof(query));\n        if (minSelector == null) throw new ArgumentNullException(nameof(minSelector));\n        if (maxSelector == null) throw new ArgumentNullException(nameof(maxSelector));\n\n        \/\/ Deze waarden hebben we al vooraf\n        minValue ??= DateTime.MinValue;\n        maxValue ??= DateTime.MaxValue;\n\n        var minPropertyName = GetPropertyName(minSelector);\n        var maxPropertyName = GetPropertyName(maxSelector);\n\n        var parameter = Expression.Parameter(typeof(T), \"x\");\n\n        var validFromExpression = Expression.Property(parameter, minPropertyName);\n        var validToExpression = Expression.Property(parameter, maxPropertyName);\n\n        var nullCheckValidFrom = Expression.Equal(validFromExpression, Expression.Constant(null));\n        var validFromCondition = Expression.LessThanOrEqual(validFromExpression, Expression.Constant(minValue, typeof(DateTime?)));\n\n        var nullCheckValidTo = Expression.Equal(validToExpression, Expression.Constant(null));\n        var validToCondition = Expression.GreaterThanOrEqual(validToExpression, Expression.Constant(maxValue, typeof(DateTime?)));\n\n        var body = Expression.AndAlso(\n            Expression.OrElse(nullCheckValidFrom, validFromCondition),\n            Expression.OrElse(nullCheckValidTo, validToCondition)\n        );\n        return query.Where(Expression.Lambda&lt;Func&lt;T, bool>>(body, parameter));\n    }\n\n    private static string GetPropertyName&lt;T, TProp>(Expression&lt;Func&lt;T, TProp>> propertyExpression)\n    {\n        if (propertyExpression.Body is MemberExpression memberExpression)\n            return memberExpression.Member.Name;\n        throw new ArgumentException(\"Invalid property expression\", nameof(propertyExpression));\n    }\n\n    public static IQueryable&lt;T> InDateTimeRangeIf&lt;T>(this IQueryable&lt;T> source,\n        bool condition,\n        Expression&lt;Func&lt;T, DateTime?>> minSelector,\n        Expression&lt;Func&lt;T, DateTime?>> maxSelector,\n        DateTime? minValue,\n        DateTime? maxValue)\n    { \n        return condition? source.InDateTimeRange(minSelector, maxSelector, minValue, maxValue) : source;\n    }\n}\n<\/pre><\/div>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-8120","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts\/8120","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/comments?post=8120"}],"version-history":[{"count":4,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts\/8120\/revisions"}],"predecessor-version":[{"id":8192,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts\/8120\/revisions\/8192"}],"wp:attachment":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/media?parent=8120"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/categories?post=8120"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/tags?post=8120"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}