linq to sql, select by dates that match to month and year from dates array

Question

I need to select rows according to month and year from dates array.

the code:

        int m0 = dates[0].Month;
        int m1 = dates[1].Month;
        int m2 = dates[2].Month;
        int y0 = dates[0].Year;
        int y1 = dates[1].Year;
        int y2 = dates[2].Year;
        var segments = (from sh in context.sh
                         where (sh.Report_Date.Month == m0 && sh.Report_Date.Year == y0 ||
                                sh.Report_Date.Month == m1 && sh.Report_Date.Year == y1 ||
                                sh.Report_Date.Month == m2 && sh.Report_Date.Year == y2)

First, is very ugly to create 6 different variable for that. and what if i don't have the array count? there is another way?

I would create an object `Date` and redefine it's `Equals` to match your criteria — Greggz, Jan 17 '18 at 09:28
A little more enfatic this time ***redefine it's `Equals` to match your criteria*** — Greggz, Jan 17 '18 at 09:35
@Greggz said redefine - which is: `overrirde` (in C#). You can specify own equal method, which would be independent of day :) — Michał Turczyn, Jan 17 '18 at 09:36
@Greggz, did not understand. Of course, you can create any object or structure and redefine Equals and any operator, but how LINQ to SQL can materialize it? Report_Date has DateTime type and with current query LINQ to SQL will provide 6 variables and call sql function DATEPART() for each. In case of comparison with custom type you just will get exception. — vitalygolub, Jan 17 '18 at 11:02
@vitalygolub Easily. While iterating in Linq you create the same type `Date` with the variables coming from Report_Date.. — Greggz, Jan 17 '18 at 11:41

score 1 · Answer 1 · answered Jan 17 '18 at 15:15

ok, just to finish the gestalt :-) You can use System.Data.Entity.DBFunctions to translate to not-obvious sql functions

DateTime d1 = new DateTime(2015, 01, 01);
DateTime d2 = new DateTime(2016, 05, 01);
DateTime d3 = new DateTime(2017, 03, 01);

var segments = from sh in context.sh
where 
    (DbFunctions.DiffMonths(d1, sh.Report_Date) == 0 || 
    DbFunctions.DiffMonths(d2, sh.Report_Date) == 0 ||
    DbFunctions.DiffMonths(d3, sh.Report_Date) == 0 
    )
select sh;

will be translated to

SELECT
    [Extent1].[ID] AS [ID],
    [Extent1].[Report_Date] AS [Report_Date],
    [Extent1].[Value] AS [Value]
    FROM [dbo].[sh] AS [Extent1]
    WHERE (0 = (DATEDIFF (month, @p__linq__0, [Extent1].[Report_Date]))) OR (0 = (DATEDIFF (month, @p__linq__1, [Extent1].[Report_Date]))) OR (0 = (DATEDIFF (month, @p__linq__2, [Extent1].[Report_Date])))


-- p__linq__0: '01.01.2015 00:00:00' (Type = DateTime2, IsNullable = false)

-- p__linq__1: '01.05.2016 00:00:00' (Type = DateTime2, IsNullable = false)

-- p__linq__2: '01.03.2017 00:00:00' (Type = DateTime2, IsNullable = false)

It is work, thanks . dates.ForEach(date => segments = segments.Where(s => DbFunctions.DiffMonths(date, s.Report_Date) == 0)); — yantrab, Feb 06 '18 at 10:31
Exactly in my example, just replacement of your query with 3 different dates `s=> something || something else || something different` :-). Here https://stackoverflow.com/questions/857973/linq-to-entities-sql-in-clause is explained how to implement sql IN if you have to much dates — vitalygolub, Feb 06 '18 at 11:22
what means dynamic, can you get it as IEnumerable before call? — vitalygolub, Feb 06 '18 at 11:27

score 0 · Answer 2 · answered Feb 06 '18 at 12:07

0

Thanks to vitalygolub, and with PredicateBuilder , this is what i did:

        dates.ForEach(date =>
        {
            predicate = predicate.Or(s => DbFunctions.DiffMonths(date, s.Report_Date) == 0);
        });

answered Feb 06 '18 at 12:07

yantrab

2,482
4
31
52

linq to sql, select by dates that match to month and year from dates array

2 Answers2