CODEWITHSUNDEEP
c#sql-serverlinq
Florian Van Dillen
Florian Van Dillen

Reputation: 15

Using T-SQL DATEFROMPARTS() in C# Linq

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

Upvotes: 0

Views: 678

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239714

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.1

(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.

1In 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.

Upvotes: 1

jdweng
jdweng

Reputation: 34419

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

Upvotes: 1

Related Questions