SQL not in to Linq

Question

I have a (Oracle)SQL query that I want to translate to Linq to use on a NHibernate context. What the query does is to get me all the Functions that has been deleted from a given Module (ie. they don't exist in the given module but are linked to a predecessor Module)

"SELECT * FROM Function WHERE ModID = " + module.PredecessorID + "
   AND FncName NOT IN
      (SELECT FncName FROM Function WHERE ModId = " + module.ModID + " )"

Here is a image to explain the data model

enter image description here

I'm looking for an expression with the "dot notation", something like this:

DBContext.GetAll<Function>()
    .Where(x => x.Module.ModID == module.PredecessorID)...

Raphaël Althaus · Accepted Answer · 2014-01-30 09:43:14Z

1

You could do this in two steps (for readability).

var excludedNames = DbContext.GetAll<Function>()
                             .Where(x => x.ModeID == module.ModID)
                             .Select(x => x.FncName);

var result = DBContext.GetAll<Function>()
                      .Where(x => x.Module.ModID == module.PredecessorID &&
                                  !excludedNames.Contains(x.FncName));

answered Jan 30, 2014 at 9:43

Raphaël Althaus

60.6k6 gold badges106 silver badges127 bronze badges

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

4 Comments

Markus Over a year ago

Guess I could, but I would expect that NHibernate can't make that into only one SQL expression, and that would be preferable. (At least if feels like that would save some computational time right?)

Raphaël Althaus Over a year ago

@Markus : if you don't enumerate first query, this will be translated in one sql, not two.

Markus Over a year ago

don't enumerate? do you mean like var excludedNames instead of List<Function> excludedNames = ....ToList()?

Raphaël Althaus Over a year ago

well, you can use var, yes. Anyhow, if you don't put a .ToList() at the end, it's an IEnumerable<string> not a List<string> (we select FncName only, so it's no more a collection of Function.

Collectives™ on Stack Overflow

SQL not in to Linq

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related