Trying to make queries easier

Why did you write it?

Why write? Probably sunk costs.

It's just that since Source Generators came out, I've been planning to use it to see if I could do aop (now that it has built-in functionality), and I originally wanted to try to see if I could do something with orm back then, but unfortunately for various reasons, I've forgotten about that intention.

It wasn't until July of this year that the intention came back to me, and now that the energy isn't working, and I was supposed to be done with studying the principles and functional limitations.

But it's been quite a while since I wrote it, and it felt like a bit of a waste not to write it in full, and it took quite a bit more time to put something likeDapperAOT Functionality is pretty much done

After writing and feel that the light copy copy function is not interesting, so the previous in order to write some less repetitive code in the company to do the query customization function in the base to get a hand, anyway, the cost has been spent so much!

While this isn't much of an innovation, it's an idea that's been around for a long time, with the most dramatic being graphql, which even made it necessary to build everyone's infrastructure, it's also an idea that's been around for a long time, and it's been around for a long time.

But more and more, all the way to the Fourth of July, so far can only be regarded as the main body is completed, there are still a lot of details have not been done!

A lot of conversions Parsing is handwritten, not because of the writing of nb, just the introduction of other libraries compatible with may be a bit of a problem, the scene is not much, recursive traversal is enough!

I don't have time to mess with nuget or anything else, there are quite a few bugs, so I'll mess with it later when it's more complete, it's all for my own amusement anyway.

I'm getting older. I'm getting slower and slower.

what can I do?

1. db mapping to entities

look as ifDapperAOT As with the use ofSource Generators Generate the necessary code during the build to help you use sql more easily.

Theoretically, you can also performNative AOT Deployment

It's nothing really. Give me a chestnut.

public async Task<object> OldWay()
{
    var a = ();
    using var dd = await ("""
SELECT count(1)
FROM Weather;
SELECT *
FROM Weather;
""");
    var t = await <int>();
    var r = await <string>().ToListAsync();
    return new { TotalCount = t, Rows = r };
}

2. Make query coding simple and support more complex conditions

By defining some simple query rules, we can convert a query into a db / api / es query statement ....

Currently, I've only done the db conversion, and I haven't had time to do the full adaptation test, es, mongodb, etc. I'll have time to do that later.

In theory, though, we could do this:

http query string / body  |------>  select statement    |------>  db (sqlite / mysql/ sqlserver / PostgreSQL)
Expression code           |------>                      |------>  es
                                                        |------>  mongodb
                                                        |------>  more .....

interlocutoryselect statement The layer's defined, the front end's converted, the back end's theorized and adapted, and anything can be done.

2.1 An api example

Code exmples:

First, define an entity configuration that lists which fields can be looked up, sorted, and filtered.

[Db("Demo")]
[Table(nameof(Weather))]
public class Weather
{ 
 [Select, Where, OrderBy]
 public string Name { get; set; }

 [Select(Field = "Value as v"), Where, OrderBy]
 public string V { get; set; }

 [Select(NotAllow = true)]
 public string Test { get; set; }
}

Then define the query interface

[HttpGet]
public async Task<object> Selects() // You can do some of the fields yourself, authorization check [FromQuery, Required] string name)
{
    return await <Weather>();
}

Then you can let the users spell out various conditions for them to fulfill their own scenarios, so you can touch the fish yourself for a little while longer

curl --location 'http://localhost:5259/weather?where=not (name like '%e%')&TotalCount=true'

Response

{
    "totalCount": 1,
    "rows": [
        {
            "name": "H",
            "v": "mery!"
        }
    ]
}

2.2 The same can be done to make the query code simpler

Code exmples:

In fact, many orm's offer the ability to use Expression to achieve similar or more complex results.

Here to consider the workload, restful api and other data query implementation support degree is not the same, at present only do the basic filter support, join what do not engage in, even if you engage in more than half of them will be scolded, directly write the sql is not better?

for example, the following query Weather which name no Contains 'e'

public async Task<object> DoSelects()
{
    return await (<Weather>().Where(i => !("e")).WithTotalCount());
}

Here is a very simple introduction, complex, how to realize the not write, tired, can not write, to have an interest in gayhub can look at the source code/fs7744/

Here's another list of filter operator support

Query in api

Both has func support use query string or body to query

body or query string will map to Dictionary<string, string> to handle

operater

such filter operater just make api more restful (Where=urlencode(complex condition) will be more better)

• {{nl}} is null
  • query string ?name={{nl}}
  • body {"name":"{{nl}}"}
• {{eq}} Equal =
  • query string ?name=xxx
  • body {"name":"xxx"}
• {{lt}} LessThan or Equal <=
  • query string ?age={{lt}}30
  • body {"age":"{{lt}}30"}
• {{le}} LessThan <
  • query string ?age={{le}}30
  • body {"age":"{{le}}30"}
• {{gt}} GreaterThan or Equal >=
  • query string ?age={{gt}}30
  • body {"age":"{{gt}}30"}
• {{gr}} GreaterThan >
  • query string ?age={{gr}}30
  • body {"age":"{{gr}}30"}
• {{nq}} Not Equal !=
  • query string ?age={{nq}}30
  • body {"age":"{{nq}}30"}
• {{lk}} Prefix Like 'e%'
  • query string ?name={{lk}}e
  • body {"name":"{{lk}}e"}
• {{rk}} Suffix Like '%e'
  • query string ?name={{rk}}e
  • body {"name":"{{rk}}e"}
• {{kk}} Like '%e%'
  • query string ?name={{kk}}e
  • body {"name":"{{kk}}e"}
• {{in}} in array (bool/number/string)
  • query string ?name={{in}}[true,false]
  • body {"name":"{{in}}[\"s\",\"sky\"]"}
• {{no}} not
  • query string ?age={{no}}{{lt}}30
  • body {"age":"{{no}}{{lt}}30"}

Func Fields:

• Fields return some Fields , no Fields or Fields=* is return all
  • query string ?Fields=name,age
  • body {"Fields":"name,age"}
• TotalCount return total count
  • query string ?TotalCount=true
  • body {"TotalCount":"true"}
• NoRows no return rows
  • query string ?NoRows=true
  • body {"NoRows":"true"}
• Offset Offset Rows index
  • query string ?Offset=10
  • body {"Offset":10}
• Rows Take Rows count, default is 10
  • query string ?Rows=100
  • body {"Rows":100}
• OrderBy sort result
  • query string ?OrderBy=name:asc,age:desc
  • body {"OrderBy":"name:asc,age:desc"}
• Where complex condition filter
  • query string ?Where=urlencode( not(name like 'H%') or name like '%v%' )
  • body {"Where":"not(name like 'H%') or name like '%v%'"}
  • operaters
    • bool
      • example true or false
    • number
      • example 12323 or 1.324 or -44.4
    • string
      • example 'sdsdfa' or 'sds\'dfa' or "dsdsdsd" or "fs\"dsf"
    • = null is null
      • example name = null
    • = Equal
      • example name = 'sky'
    • <= LessThan or Equal
      • example age <= 30
    • < LessThan
      • example age < 30
    • >= GreaterThan or Equal
      • example age >= 30
    • > GreaterThan
      • example age > 30
    • != Not Equal
      • example age != 30
    • like 'e%' Prefix Like
      • example name like 'xx%'
    • like '%e' Suffix Like
      • example name like '%xx'
    • like '%e%' Like
      • example name like '%xx%'
    • in () in array (bool/number/string)
      • example in (1,2,3) or in ('sdsdfa','sdfa') or in (true,false)
    • not
      • example not( age <= 30 )
    • and
      • example age <= 30 and age > 60
    • or
      • example age <= 30 or age > 60
    • ()
      • example (age <= 30 or age > 60) and name = 'killer'