System.Linq.Dynamic - use SELECT inside WHERE statement

c# dynamic-linq linq sql

Question

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?

1
1
4/20/2017 6:49:37 PM

Accepted Answer

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 && !@2.Any(Uid == outerIt.Uid && FromRevision <= @1 && TillRevision > @1)",
    tillRevision, // @0
    fromRevision, // @1,
    query // @2
);
2
4/20/2017 7:29:34 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow