System.Linq.Dynamic - use SELECT inside WHERE statement

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?

Is there a reason you need to use Dynamic Linq and not build an expression yourself? — NetMage
– NetMage, Commented Apr 20, 2017 at 22:46
Yes, I don't know at designtime what types will come to this constructor, filters and so on. — Artem Makarov
– Artem Makarov, Commented Apr 21, 2017 at 6:44

Ivan Stoev · Accepted Answer · 2017-04-20 19:29:34Z

2

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
);

answered Apr 20, 2017 at 19:29

Ivan Stoev

208k17 gold badges356 silver badges376 bronze badges

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

4 Comments

Artem Makarov Over a year ago

Unfortunately, I get exception like "No property or field 'outerIt' exists in type 'MyType' ".. It seems that parser convert my query parameter to a real objects collection and don't know what "outerIt" is. Any ideas? I've found that there is some problems with outerIt statement, but now sure that it's my case..

Ivan Stoev Over a year ago

I don't know, it worked for me. May be you are on older version? I've tested with System.Linq.Dynamic by Microsoft, v1.0.6

NetMage Over a year ago

Apparently outerIt is an extension to the original Dynamic library that some versions implement, the nuget version 1.07 should have it, though it looks to me like it may still have a bug in its implementation.

Artem Makarov Over a year ago

Thank you, man! That works, I've removed my local DLL and load from NuGets, System.Linq.Dynamic.Core. Don't know why, but System.Linq.Dynamic lib don't work, but .Core is ok!

Collectives™ on Stack Overflow

System.Linq.Dynamic - use SELECT inside WHERE statement

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