Converting from Sql to Linq

Question

I have, what I thought was a pretty straight-forward query.

In normal Sql this would read:

SELECT    [column names]
FROM      agentscheduledetail
WHERE     (date = '2012-07-04') AND 
           (
            exception = 'Break (No Sign Off)' OR
            exception = 'Break' OR
            exception = 'Break (Signed Out)'
           )

This returns approx 900 records.

However, when I try to enter this into my controller, I end up with around 300,000 records - so I think my AND and ORs are not working. I've tried Linqer, but can't get it to work (I'm aware this may not be actual LINQ but the equivalent query in VS - if there is a linq version... I'd be grateful for that too if possible).

My controller query is:

        var dte = DateTime.Today;

        return View(db.agentscheduledetails.Where
           (
            d => d.date == dte && d.agentName.StartsWith("ta") && 
             (
              d.exception == "Break (No Sign Off)" || 
              d.exception == "Break" || 
              d.exception == "Break (Signed Out)"
             )
            ).ToList()
           );

Can anyone either a) let me know where I'm going wrong with my && || (and/or), or b) is there a way of stepping through the code in VS, to see what the above query translates to in normal SQL so I can try to figure out where I'm going wrong?

Thanks for any help,

Mark

You haven't specified whether you're using LINQ to SQL or Entity Framework - but either way, yes, there are ways of looking at the generated SQL. See DataContext.Log for example in LINQ to SQL. — Jon Skeet
– Jon Skeet, Commented Jul 4, 2012 at 8:46
Setup the DB context to output sql queries to a log file or the debugger windows. Check that's correct. — Steve B
– Steve B, Commented Jul 4, 2012 at 8:46
Hi - it is the Entity Framework - I generated my classes, and clicked Add controller in VS, and let it build the default controller code using EF. All I am trying to do is filter the basic query it generates in the GET method. Contains returns the same. How do you setup the DB Context to output queries - the DBContext was also setup automatically by VS when I created the controller. Thanks. — Mark
– Mark, Commented Jul 4, 2012 at 8:52
if you do create a query object e.g. var q=...where... (missing out the ToList() on the end. Visual Studio debugger will show you the Generated SQL if you inspect the value. — Bob Vale
– Bob Vale, Commented Jul 4, 2012 at 8:53

Jon Crowell · Accepted Answer · 2012-07-04 14:56:05Z

1

The following is perhaps a simplified version of what you are trying to do, also your LINQ contains an additional statement compared to the SQL where it is comparing the agent name?

var currentDate = DateTime.Today;
var exceptionTypes = new List<string>() { "Break (No Sign Off)", 
                                          "Break", "Break (Signed Out)" };

db.agentscheduledetails.Where(d => d.date == currentDate && 
                                           exceptionTypes.Contains(d.exception));

One thing that you could try is getting hold of a copy of LinqPad, this will let you run your LINQ statement against a database and will show you what the generated SQL statement is.

edited Jul 4, 2012 at 14:56

Jon Crowell

22.4k15 gold badges94 silver badges113 bronze badges

answered Jul 4, 2012 at 8:50

daz-fuller

1,2311 gold badge10 silver badges18 bronze badges

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

2 Comments

Mark Over a year ago

hi - thanks - that works perfect. Can you not use && and || in the statements I was making? (I'll mark as answer as soons as SO allows) - cheers, Mark

daz-fuller Over a year ago

If I run something similar in LinqPad then I get the desired result and the correct SQL. I'm not sure about your set up though, if it's the Entity Framework then I'm not sure what will be happening, but in normal Linq to SQL it should work fine? This method tends to be the preferred though as it's generally simpler to write.

Nik · Accepted Answer · 2012-07-04 08:49:40Z

0

Aside from anything else,

    d.agentName.StartsWith("ta")

does not appear in your original sql...?

answered Jul 4, 2012 at 8:49

Nik

2,7061 gold badge23 silver badges39 bronze badges

1 Comment

Mark Over a year ago

you're correct - I added that to see if it would filter the records anymore - it didn't...

Collectives™ on Stack Overflow

Converting from Sql to Linq

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related