Using T-SQL DATEFROMPARTS() in C# Linq

Question

I have the following T-SQL query which I'm trying to convert into C# code:

SELECT 
    *, DATEFROMPARTS(Jaar, Maand, 1) 
FROM 
    EAN_Budget_Meetdata_Factuur
WHERE
    EID = 1 AND
    Bron = 'B' AND
    DATEFROMPARTS(Jaar, Maand, 1)  BETWEEN '20161101' AND '20170101'

The table contains a column Year and Month, and I'm querying the table based on two known DateTimes (a period).

The query is meant to retrieve the table rows that apply to this period based on the years and months between the two DateTimes.

What is the best way to convert this query into C#? I've so far come up with the following, without success:

var query = from b in CT2.EAN_Budget_Meetdata_Factuur
            let JaarTest = b.Jaar + "-" + b.Maand + "-01"
            where 
                b.EID == 1 &&
                b.Bron == "B" &&
                JaarTest == "something"
                select new
                {
                    JaarTest = b.Jaar + "-" + b.Maand + "-01"
                };

Why not use DateTime's constructor that takes year, month, and day? — Zohar Peled
– Zohar Peled, Commented Nov 24, 2017 at 11:59
@Damien_The_Unbeliever No, this differs every time. Sometimes it's a month, sometimes 3 months, sometimes more. — Florian Van Dillen
– Florian Van Dillen, Commented Nov 24, 2017 at 12:03

Damien_The_Unbeliever · Accepted Answer · 2017-11-24 14:24:35Z

1

If you're using DATEFROMPARTS on table columns you've already lost any opportunity for indexes to be useful on the database side - so I'd instead recommend working in total months.¹

(year * 12) + months is a simple calculation that you can perform both with the table columns and whatever start/end points you want to query for and you then can perform simple int range checks.

¹In the alternative, if you hadn't realised that this was going to deny you any indexing ability, you might want to consider adding a computed column on which you apply an index - in which case the column definition would use the function (either the total months calculation I give here or the original DATEFROMPARTS one) and you'd then query that column easily.

edited Nov 24, 2017 at 14:24

answered Nov 24, 2017 at 12:01

Damien_The_Unbeliever

241k28 gold badges358 silver badges470 bronze badges

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

Comments

jdweng · Accepted Answer · 2017-11-24 11:59:13Z

1

Try following :

    class Program
    {
        static void Main(string[] args)
        {
            var query = EAN_Budget_Meetdata_Factuur.factuurs
                    .Where(x => (x.eid == 1) && (x.bron == "B") && (x.date >= new DateTime(2016, 11, 1)) && (x.date <= new DateTime(2017, 1, 1)))
                    .Select(x => new { eid = x.eid, bron = x.bron, date = x.date })
                    .ToList();
        }
    }
    public class EAN_Budget_Meetdata_Factuur
    {
        public static List<EAN_Budget_Meetdata_Factuur> factuurs = new List<EAN_Budget_Meetdata_Factuur>();

        public int eid { get; set; }
        public string bron { get; set; }
        public DateTime date { get; set; }
    }

answered Nov 24, 2017 at 11:59

jdweng

34.5k3 gold badges17 silver badges19 bronze badges

Collectives™ on Stack Overflow

Using T-SQL DATEFROMPARTS() in C# Linq

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related