CODEWITHSUNDEEP
c#sql-serverlinq
yantrab
yantrab

Reputation: 2662

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

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?

Upvotes: 0

Views: 619

Answers (2)

yantrab
yantrab

Reputation: 2662

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);
        });

Upvotes: 0

vitalygolub
vitalygolub

Reputation: 735

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)

Upvotes: 1

Related Questions