1

Given the following classes,

public class MyClass
{
    public string Property { get; set; } //mapped to column 'property'
}

public class MyContext : DbContext
{
    public DbSet<MyClass> MyClasses { get; set; } //mapped to table dbo.MyClasses
}

I'm trying to generate the following SQL request, using Linq to SQL and EF Core 3.1.

SELECT * FROM dbo.MyClasses
WHERE property > 'constant'

Any help appreciated.

Attempt 1:

var result = dbContext.MyClasses.Where(c => c.Property > "constant").ToList();

//Does not compile

Attempt 2:

var result = dbContext.MyClasses.Where(c => c.Property.CompareTo("constant") > 0).ToList();

//SELECT * FROM dbo.MyClasses
//WHERE
//CASE                                                
//  WHEN property  = 'constant' THEN 0  
//  WHEN property  > 'constant' THEN 1  
//  WHEN property  < 'constant' THEN -1    
//END > 0
Ivan Stoev
  • 195,425
  • 15
  • 312
  • 343
Romain Vergnory
  • 1,496
  • 15
  • 30
  • 1
    Linq to SQL, Entity Framework, and EF Core 3 are all **different things**. Please remove the two incorrect tags (i.e. keep EF Core only). Please remove 'LINQ to SQL' terminology from the question also. – mjwills Jan 13 '20 at 11:25
  • 1
    The Attempt 2 doesn't produce the same results, even if the SQL is different? – Magnetron Jan 13 '20 at 11:35
  • The main problem is that `string `data type does not define comparison operators, so you have to use `string.Compare` or `string.CompareTo` methods, which currently have no good SQL translation. Either accept the current inefficient translation and wait RF Core to improve it eventually in some future version, or use the solution/workaround from my answer to the post marked as "duplicate". – Ivan Stoev Jan 13 '20 at 12:23

1 Answers1

0

As an alternative, you can use raw SQL:

var id = 1;
dbContext.MyClasses
    .FromSqlInterpolated("SELECT * FROM dbo.MyClasses WHERE property > {id}")
    .ToList<MyClass>();

It is possible to read more here.

StepUp
  • 36,391
  • 15
  • 88
  • 148
  • 1
    `new SqlParameter("@id", 1)` creates a parameter whose SqlDbType parameter is 1 (Binary) without any value. Enums are essentially tagged integers so `SqlParameter (string, System.Data.SqlDbType)` is a better match for this call than `SqlParameter(string,Object)` – Panagiotis Kanavos Jan 13 '20 at 13:16
  • @PanagiotisKanavos thanks for the great comment. I've updated my answer with a little bit another solution. Thanks! – StepUp Jan 13 '20 at 13:28
  • 1
    In EF Core 3.x it should be [FromSqlRaw or FromSqlInterpolated](https://learn.microsoft.com/en-us/ef/core/querying/raw-sql). You can write `.FromSqlRaw("SELECT * FROM dbo.MyClasses WHERE property > @id", id)` now – Panagiotis Kanavos Jan 13 '20 at 13:36
  • @PanagiotisKanavos thank a lot, I've updated an answer!:) – StepUp Jan 13 '20 at 13:42