subquery in where condition in linq

Question

I want to convert the below query to Linq.

SELECT a.taskid, a.status                               
FROM laqTasks a WITH (NOLOCK)
LEFT JOIN laqEmployees b WITH (NOLOCK) ON a.lastupdatedby = b.employeeid
WHERE (a.duty IN (SELECT SyncDutyId FROM laqSyncDutyList)
        OR (a.maint = 1)
    ) 
AND a.nvupdatesw = 1

Thanks.

That's the first time I've seen `WITH (NOLOCK)` used anywhere else but our shop. If you want to retain that functionality, consider using a Raw query. https://learn.microsoft.com/en-us/ef/core/querying/sql-queries — Robert Harvey, Jul 28 '23 at 13:27
@RobertHarvey you mustn't hang out in the `sql-server` tag much. :) Posters are regularly putting up code using `WITH (NOLOCK)` hints and rarely accept the advice that "it's not a go faster switch," or that it's going to give them dirty reads, etc.. — AlwaysLearning, Jul 28 '23 at 14:16
@AlwaysLearning: I work for a couple of very smart people who have made it a rule that you must use a NOLOCK everywhere you use a SELECT, despite my protestations to the contrary. They claim that it's not about performance, but about scalability. — Robert Harvey, Jul 28 '23 at 14:18
It's definitely a devil's bargain. So long as people make the decision knowing and accepting the consequences there's not much you can do about it. — AlwaysLearning, Jul 28 '23 at 14:20
*"I work for a couple of very smart people who have made it a rule that you must use a NOLOCK everywhere you use a SELECT"* I wouldn't call that "smart" @RobertHarvey , if I'm honest. If they truly "must" use it, why are they not changing the isolation level of the transaction? Why *spam* the hint? There's plenty of articles on why `NOLOCK` is avoided. [I'm using NOLOCK; is that bad?](https://sqlblog.org/nolock) is a great place to start. — Thom A, Jul 28 '23 at 14:27
Also, for the OP, aliases like `a` is for `Customer` and `b` is for `Account`, or `t1` is for `Purchase` and `t2` is for `Transaction` aren't helpful for you or others that want to read your code. Use meaningful and *consistent* aliases for your objects. I suggest a read of [Bad Habits to Kick : Using table aliases like (a, b, c) or (t1, t2, t3)](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-or-t1-t2-t3). — Thom A, Jul 28 '23 at 14:29
@RobertHarvey Still a bad idea. If blocking was an issue, why don't they use `SNAPSHOT` isolation? And if outright performance was the goal, and no blocking was involved, then why not just `TABLOCK` the whole thing. It's just a bad idea all round. — Charlieface, Jul 28 '23 at 14:34
To provide a bit more context, years ago there was an uber stored procedure that took 2 minutes to run, and pretty much blocked the entire database during that 2 minutes. With Microsoft's help, they got it down to 10 seconds; using NOLOCK was part of the solution. Since then, they sprinkle NOLOCK like magic pixie dust on every SELECT query they find. — Robert Harvey, Jul 28 '23 at 14:58
Snapshot isolation wasn't available for sql server <=2000 i think. My previous work also liked the NOLOCK sprinkler, @RobertHarvey, and i don't blame them, it was a legacy base, and people really liked to run stuff in production with begin transaction, and then went for breakfast. Change of isolation with set isolation_level was met with blank stares and "we don't wanna risk anything". After a while, one gets used to the magic dust — siggemannen, Jul 28 '23 at 15:17
@RobertHarvey I was doing some consulting for a group a number of years who had a procedure similar to the one you described. Despite my suggestion it was a bad idea they splattered no lock hints everywhere in over a thousand procedures. This company provided debit cards for some business segments so getting the data right was very important. Much to the dismay of the DBA team they allowed transactions with no money and refused others. All because of nolock. They later spent another few months going back and removing all the hints. With thousands of dollars lost, their DBA team listened to me. — Sean Lange, Jul 28 '23 at 17:01
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Jul 28 '23 at 21:08
Rather than asked for a translation, which is asking for trouble, instead provide your requirements in the form of sample date and desired results, and it usually helps to show your attempt. — Dale K, Jul 29 '23 at 01:11

score 0 · Answer 1 · answered Jul 28 '23 at 14:32

The equivalent LINQ would be as follows:

var results = 
    from t in Db.laqTasks
    from e in Db.laqEmployees.Where(e => t.lastupdatedby == e.employeeid).DefaultIfEmpty()
    where (
          Db.laqSyncDutyList.Select(sd => sd.SyncDutyId).Contains(t.duty)
          || t.maint == 1
      ) && t.nvupdatesw == 1
    select new {
        t.taskid,
        t.status                               ,
    };

It's unclear if those == 1 actually refer to bit columns, which are normally translated as bool.

Note the use of DefaultIfEmpty to get a left join. Having said that, you are not using the results from the left join, so you could just remove it.

var results = 
    from t in Db.laqTasks
    where (
          Db.laqSyncDutyList.Select(sd => sd.SyncDutyId).Contains(t.duty)
          || t.maint == 1
      ) && t.nvupdatesw == 1
    select new {
        t.taskid,
        t.status                               ,
    };

The same in pure method syntax rather than query syntax:

var results = 
    laqTasks.Where(t => (
          laqSyncDutyList.Select(sd => sd.SyncDutyId).Contains(t.duty)
          || t.maint == 1
        ) && t.nvupdatesw == 1
    ).Select(t =>
        new
        {
            t.taskid,
            t.status                               ,
        });

Do not use NOLOCK, it has serious data integrity implications. If you need to prevent blocking, use a Snapshot isolation level.

Thanks for the suggestion and the code. I will remove unlock. — shanthakumar n, Jul 28 '23 at 15:29

subquery in where condition in linq

1 Answers1