1

I'm working on custom filter with System.Linq.Dynamic, everything was ok until i start the fight with BETWEEN.

I created a custom class called Filtro, it has this propertys .

public string PropertyName { get; set; }
public string Value1 { get; set; }
public string Value2 { get; set; }

Then, i basically filter a IQueryable object with the parameters of that class

I'm trying this:

var query = db.Where("@0 > @1 AND @0 < @2", filtro.PropertyName, filtro.Value1, filtro.Value2);

My problem is that the parameters are generated incorrectly in the query.

Example:

filtro.PropertyName = "example";
filtro.Value1 = "10";
filtro.Value2 = "20";

When i generate query without parameters 

var query = db.Where("example>10 && example<20");

It generates this:

SELECT 
    [Extent1].[randomField] AS [randomField], 
    [Extent1].[example] AS [example], 
    FROM [dbo].[Transformador] AS [Extent1]
    WHERE ([Extent1].[example] > cast(10 as decimal(18))) AND ([Extent1].[example] < cast(20 as decimal(18)))
    ORDER BY [Extent1].[randomField] ASC}

But when i use parameters (as i show before) i'm getting this

SELECT 
    [Extent1].[randomField] AS [randomField], 
    [Extent1].[example] AS [example]
    FROM [dbo].[Transformador] AS [Extent1]
    WHERE (N'example' > N'10') AND (N'example' < N'20')
    ORDER BY [Extent1].[randomField] ASC

I guess I'm using the parameters of Linq Dynamics wrong, but I did not find anything similar going around on the internet

I hope someone know what the problem is, thanks in advance!

Juan Salvador Portugal
  • 1,233
  • 4
  • 20
  • 38
  • What is wrong with the `N'` prefix? That's [perfectly normal SQL syntax](https://stackoverflow.com/questions/10025032/what-is-the-meaning-of-the-prefix-n-in-t-sql-statements). Is it causing your queries to fail? – DavidG Jul 04 '18 at 15:19
  • https://stackoverflow.com/questions/10025032/what-is-the-meaning-of-the-prefix-n-in-t-sql-statements – Peter Bons Jul 04 '18 at 15:20
  • @DavidG the query's return 0 rows :S – Juan Salvador Portugal Jul 04 '18 at 15:24
  • @PeterBons the wierd thing is normal Linq dont add's the `N` character, probably, the problem is it doesn't recognize, the second and third parameters as number, if i pass them as numbers, it thrown an exception :S – Juan Salvador Portugal Jul 04 '18 at 15:26
  • Yeah seems illogical. Anyway, I wouldn't give it much thought. There is nothing going wrong here. – Peter Bons Jul 04 '18 at 15:34

1 Answers1

3

The problem is that Dynamic Linq is assuming the example value you are passing in is a value and not a column name. So the query returns no rows because the string example is not between the strings 10 and 20.

The N' prefix is perfectly normal SQL syntax and is only coercing the string into an NVARCHAR type instead of VARCHAR. See this for more info. For you code however, try this for example:

var sql = $"{filtro.PropertyName} > @0 AND {filtro.PropertyName} < @1";
var query = db.Where(sql, filtro.Value1, filtro.Value2);

Note: this leaves you open to SQL injection so is quite dangerous.

DavidG
  • 113,891
  • 12
  • 217
  • 223