CODEWITHSUNDEEP
c#sqllinqdynamic-linq
Artem Makarov
Artem Makarov

Reputation: 874

System.Linq.Dynamic - use SELECT inside WHERE statement

I need to generate SQL query dynamically using System.Linq.Dynamic like this:

SELECT 
    [Extent1].[FromRevision] AS [FromRevision], 
    [Extent1].[Field1] AS [Field1], 
    [Extent1].[TillRevision] AS [TillRevision], 
    [Extent1].[Field2] AS [Field2], 
    [Extent1].[Date1] AS [Date1], 
    [Extent1].[LastChangeDate] AS [LastChangeDate], 
    [Extent1].[Field3] AS [Field3]
    FROM [log].[MyTable] AS [Extent1]
    WHERE (([Extent1].[FromRevision] <= @p__linq__0) AND ([Extent1].[TillRevision] > @p__linq__1) AND 
    ( NOT EXISTS (SELECT 
        1 AS [C1]
        FROM [log].[MyTable] AS [Extent2]
        WHERE ([Extent2].[Field1] = [Extent1].[Field1]) AND ([Extent2].[FromRevision] <= @p__linq__2) AND ([Extent2].[TillRevision] > @p__linq__3)
    )))

The code above was generated by Linq for generic IQueryable, like this:

query.Where(o =>
            //ADDED
            (   o.FromRevision <= tillRevision &&
                o.TillRevision > tillRevision &&
                !query.Any(o1 =>
                    o1.Uid == o.Uid &&
                    o1.FromRevision <= fromRevision &&
                    o1.TillRevision > fromRevision)
            )

But is it possible to get same result, same SELECT result using Dynamic Linq and not generic IQueryable?

Upvotes: 1

Views: 710

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205579

You can utilize the optional params object[] values argument to pass some parameters to the dynamic query (like query, tillRevision and fromRevision). Then you can refer to them inside the string as @0, @1, @2 based on their position.

For accessing the current range variable in scope you could use it or nothing. It will be the equivalent of your o variable. The important detail is that when you enter a nested scope (like your Any), inside that scope it or nothing will refer to what your o1 variable does. In order to access the outer variable, you should use outerIt.

Here is the Dynamic LINQ equivalent of your query, hope you'll figure out how to build it dynamically:

var result = query.Where(
    "FromRevision <= @0 && TillRevision > @0 && [email protected](Uid == outerIt.Uid && FromRevision <= @1 && TillRevision > @1)",
    tillRevision, // @0
    fromRevision, // @1,
    query // @2
);

Upvotes: 2

Related Questions