ASP.NET MVC SQL

Question

I have the following code:

public IList<ProductionRuns> List()
        {
            var Shows3Months = (from s in _db.ProductionRuns
                                where s.startDate <= DATEADD(month, 3, GETDATE()) and s.endDate > = GETDATE() select s);

            return Shows3Months.ToList();
        }

The idea is that it will show a list of productions for the next 3 months so it compares against the production start date and end date with the current date. Can anyone help me fix the SQL statement? Thanks

Richard · Accepted Answer · 2011-04-08 12:35:18Z

2

That's not SQL, that's a LINQ comprehension expression: I think you have your languages confused.

Either stay in C# and your model, or execute SQL on the database.

In C# (with some guessing to fill in the model) I think your expression needs to be something like:

var d1 = DateTime.Today.AddMonths(-3);

from s in _db.ProductionRuns
where s.startDate <= d1 && s.endDate >= DateTime.Today
select s

EDIT: Pre-calculate date so LINQ to SQL doesn't try and translate it to SQL.

edited Apr 8, 2011 at 12:35

answered Apr 8, 2011 at 10:22

Richard

110k21 gold badges214 silver badges279 bronze badges

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

2 Comments

archil Over a year ago

This will not work as AddMonth is not supported by linq to sql

Richard Over a year ago

@archil OK, so just need to pre-calculate.

archil · Accepted Answer · 2011-04-08 10:28:21Z

2

public IList<ProductionRuns> List()
        {
            DateTime startDate = DateTime.Today.AddMonths(-3);
            DateTime endDate = DateTime.Today;

            var Shows3Months = (from s in _db.ProductionRuns
                                where s.startDate <= startDate and s.endDate >= endDate select s);

            return Shows3Months.ToList();
        }

answered Apr 8, 2011 at 10:28

archil

39.6k7 gold badges69 silver badges83 bronze badges

Collectives™ on Stack Overflow

ASP.NET MVC SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related