2

question is about .NET Core 2. I am not using any ORM, because my application is not a simple CRUD, so I write SQL queries myself (I only use EntityFramework for user identity).

But now I have a problem. It turns out that I should have several nearly same methods in my service that return a dataset. They are pretty the same, but SQL "WHERE" clauses are different. So I thought about having one method with lambda expression so I could write something like:

service.GetRecords<Person>(p => p.FirstName == "Jack" && p.City == "NY");

But I didn't find any way to achieve it without ORM using just plain ADO .NET. Is there some ready tool for that? I don't need it to create full SQL query. I just need it to create WHERE clause, like:

"FirstName = 'Jack' and City = 'NY'"

I tried to write something by myself (using System.Linq.Expressions) but it's really time consuming. So is there a better way or ready to use tool?

The other thing that would suit me (it would even be better) would be if there was a way to combine IDataReader with IQueryable.

Stephan Bauer
  • 9,120
  • 5
  • 36
  • 58
Adam Jachocki
  • 1,897
  • 1
  • 12
  • 28
  • That's what the ORMs do. Based on the property names it identifies column names, class names converted to table names and lambda expressions converted to SQL statements and eventually those SQL statements are executed using simple ADO.NET classes. And these all uses reflection at great extent. Basically you are trying to have your own ORM. Reflection is your way for this. – Chetan May 30 '18 at 09:40
  • 6
    Sounds like you're reinventing [Dapper](https://github.com/StackExchange/Dapper). As soon as you find that your simple ORM is no longer simple, that's usually a sign you should stop, because whatever you come up with isn't going to be as convenient, fast or tested as the existing ORMs. (Point in case: consider how to translate `NULL` values (and anything involving comparisons with `null`), `DateTime`, `float`, `double` and things like `.Contains`. It's a lot of work that's been done before.) – Jeroen Mostert May 30 '18 at 09:41
  • Possible duplicate of [How to Convert Lambda Expression To Sql?](https://stackoverflow.com/questions/10979017/how-to-convert-lambda-expression-to-sql) – GSerg May 30 '18 at 09:45
  • What you ask is the definition of an ORM, or rather, the query language of an ORM. If you want to map to *reporting* queries, you can easily map to *views* instead of tables. – Panagiotis Kanavos May 30 '18 at 09:45
  • As for combining `IDataReader` and `IQueryable`, that's the wrong question. `IDataReader` is how you read results. `IQueryable` is how you define the queries that will be used by a provider/ORM to *produce* the results. Why do you want to use Datasets anyway ? You don't need them for data binding, you don't need them for *reporting* (you can easily map to views). – Panagiotis Kanavos May 30 '18 at 09:48
  • I use DataReader to return values from db. I found how to implement IEnumerable with DataReader but IEnumerable doesn't fit me, because it doesn't stop DataReader from reading all data from database. I know that I want some kind of ORM prosthesis, but I haven't use ORM at all, because my system is not compeletely ORM friendly (it's just too complicated) and now it's too late to add full ORM functionality. So I'm looking for something simple that would fit my needs.I just need to only FILTER data on database side using lambdas. – Adam Jachocki May 30 '18 at 10:43
  • 1
    If you really want to, then sure, you can implement (or borrow) an `IQueryable` implementation somewhere that will produce SQL `WHERE` clauses. I would seriously question whether that is actually worth the effort compared to just passing the clauses as strings, though (or, if that's a little too error-prone for your tastes, a `WhereBuilder` so you can have a fluent interface a la `.Where("FirstName = @FirstName").And("City = @City").With(new { FirstName = "Jack", City = "NY")`. Dragging in `IQueryable` really seems like a lot of effort for comparatively little gain. – Jeroen Mostert May 30 '18 at 12:44
  • Leaving out `IQueryable`, simply dissecting `Expression>` is a little more manageable, but still quite a bit of work once you have to support more than simple equality predicates combined with `AND`. In any case, writing something like that would be beyond the scope of an SO answer -- it's quite a bit of code. – Jeroen Mostert May 30 '18 at 12:47
  • So the only reasonable thing seems to be to attach ORM to system and use it when I have to do such things? – Adam Jachocki May 30 '18 at 13:33

0 Answers0