LLBL 5.5.2, Adapter, C#, .net core, SQL Server

I am trying to rework my generic auto generated search framework to use TypedViews instead of entities as it's foundation to hopefully increase performance and to make my dll smaller since a lot of the entities are readonly and a TypedView would be smaller and much faster.

In the switch to TypedViews, I love how I still have LINQ for when I do have to write custom code but it appears I loose the ability to programmatically create queries using your predicate system which now kills my generic base search classes. The entity predicate system made it so easy to have a single base class algorithm that could build even very complicated queries from json inputs. Now I feel with TypedViews, I get the best performance, but everything must be hand written.

I've been reading up on Expression trees and trying to build something programmatically from passed in json and I made some progress but now that I am trying to programmatically get a where clause into it am running into some issues. For example, my current code is programmatically applying a where clause but it looks like what I am generating is somehow returning all the rows from the db (I don't see my where clause making it into the sql statement using SQL Profiler) and am assuming that C# linq must be applying my where clause in memory after all the results have been returned from sql.

Is it possible for me to generate the equivalent of either of the below programmaticaly from within a generic stongly typed base class?

var results1 = (from r in metadata.Product where r.ItemNumber.Contains("%hammer%") || r.Description.Contains("%hammer%") select new { r.ItemNumber, r.Description }).Skip(100).Take(100).ToList();

var results2 = metadata.Product.Where(r=> r.ItemNumber.Contains("%hammer%") || r.Description.Contains("%hammer%")).Select(r=>new { r.ItemNumber, r.Description }).Skip(100).Take(100).ToList();

For example, currently, I generate my own search classes for each table that inherit from a generic base class that then knows about the right dataaccessadapter D, linqmetadata M, and the typedviewrow R. The base class then uses reflection to get the 'Product' property of M and then I am able to cast that to a DataSource2<R>. At this point, I'm now doing stuff with Expression, Expression.Property, Expression.Call, etc. Is this really the best way? Is there any other easier approach? But then, I still have to solve getting my where clause into sql (and then dealing with all the stuff like in, contains, etc).

Another issue is that users may create many generic searches on the same table with 100+ columns, but each search may return different columns. I'd rather not have my web service returning all the columns all the time, so I also have unnecessary overhead at the end of my algorithm to get your rows into my web service return model that is just the columns they want. Was hoping to leverage the LLBL generated row for programmatically building the query but then just returning the columns I need straight from sql into a model that the service returns.

Any good threads or articles I've missed that could help me with solving the above?

Or should I skip trying to generate the query and results from the TypedView and somehow there is a way to reach into all that sql mapping data buried in llbl and generate/run my own sql?

Thank you

Why not stick to the entities to build your queries, and project the resultset to POCOs/DTOs?

That is what my current approach already does. I was trying to switch to the TypedViews for performance since this is all ready only queries and don't need the overhead of entities and going through all the trouble to fetch entitycollection and then converting that into a collection of my return model with just the properties I need.

Or are you somehow implying that if I ran the above linq on entity based objects and I had a custom select, that LLBL would not actually instantiate any entities? What is the performance difference like for doing a linq where select into custom dynamic object between the base context being entity and typed view based?

On another note, I found https://dynamic-linq.net/ which appears to mostly work well with LLBL TypedViews and linq. I'm able to do where, orderby, skip, and take. Select is throwing exception "Ambiguous match found"

var resultDynamic = await metaData.Product .Where("ItemNumber.Contains(@0) OR Description.Contains(@0)", "hammer") //.Select("new { ItemNumber, Description }") // if I include this, I get Ambiguous match found .OrderBy("ItemNumber,Description") .ToDynamicListAsync();

When looking at the sql being executed, it looks like the LLBL sql I am use to seeing. Of course, seems weird for me to programmatically do things by building strings to give to dynamic-linq but I have to admit the above is so much easier than the current mess that is trying to build expressions and doesn't work.

It's a bummer about the Select not working, I wonder what it's confused about. Worst case, I'm stuck still having to fetch the whole row and then have to do another select on the returned data to reduce into the return models.

My main reason is I want the best performance, least memory overhead, and smallest possible llbl dll size. Whenever I see benchmarks comparing straight ado.net, dapper, llbl, EF, etc, it seems like if I want the best performance within LLBL, I need to use TypedViews.

happyfirst wrote:

That is what my current approach already does. I was trying to switch to the TypedViews for performance since this is all ready only queries and don't need the overhead of entities and going through all the trouble to fetch entitycollection and then converting that into a collection of my return model with just the properties I need.

Or are you somehow implying that if I ran the above linq on entity based objects and I had a custom select, that LLBL would not actually instantiate any entities? What is the performance difference like for doing a linq where select into custom dynamic object between the base context being entity and typed view based?

Indeed, if you project to a poco (e.g. through a derived model ), the projection is bypassing entity materialization.

You can also opt for using TypedViews through their QuerySpec poco's btw, which do allow you to use the predicate system in Where clauses. This allows you to re-use the predicate system you have.

On another note, I found https://dynamic-linq.net/ which appears to mostly work well with LLBL TypedViews and linq. I'm able to do where, orderby, skip, and take. Select is throwing exception "Ambiguous match found"

var resultDynamic = await metaData.Product .Where("ItemNumber.Contains(@0) OR Description.Contains(@0)", "hammer") //.Select("new { ItemNumber, Description }") // if I include this, I get Ambiguous match found .OrderBy("ItemNumber,Description") .ToDynamicListAsync();

When looking at the sql being executed, it looks like the LLBL sql I am use to seeing. Of course, seems weird for me to programmatically do things by building strings to give to dynamic-linq but I have to admit the above is so much easier than the current mess that is trying to build expressions and doesn't work.

It's a bummer about the Select not working, I wonder what it's confused about. Worst case, I'm stuck still having to fetch the whole row and then have to do another select on the returned data to reduce into the return models.

My main reason is I want the best performance, least memory overhead, and smallest possible llbl dll size. Whenever I see benchmarks comparing straight ado.net, dapper, llbl, EF, etc, it seems like if I want the best performance within LLBL, I need to use TypedViews.

Dynamic linq systems use property name matching which might not work out in practice. I'd avoid these. For performance, you can use typed views indeed, or derived models and entities. The latter will fetch very fast as well as they skip the entity materialization. The dll size shouldn't be a problem, unless you're running on a very low memory system; .net's own libraries are much bigger.

So I'd opt for query spec typed views in your case: you can reuse the predicates you have and use poco typed view fetches which means you don't need entities and get what you wanted. (altho as I said, it's not really necessary to focus on dll size)

I experimented with QuerySpec but didn't like that I then lost linq and I have a lot of existing code written in linq.

The other thing is that I am programmatically regenerating the entire llbl library externally calling CliSyncWithDatabase and CliGenerator and I haven't found a way to get it to create just entities and not typed views when refreshing with the db. I end up with both and then my dll size is 10mb. I can either end up with just typed views or both. Haven't found a way to get just entities without going in by hand and doing things myself. So before a release, I try to remember to go in and delete all the typed views that I'm not using.

I have a large db and i tested dll size between all entities and all typed views and it was 5mb (entities) vs 4mb (typed views) or 10mb for both. I had thought the typed views would be smaller. Trying to get my webservice to restart even faster than it currently does. Plus, the regeneration takes that much longer when doing both. Once an llbl library has a table or view mapped as an Entity, what purpose does having it also mapped to a TypedView then serve? Is that just for performance and it's up to the dev to pick the right one for the moment? Or is it that if you need to read/write map to entity and if just read, use typedview?

happyfirst wrote:

I experimented with QuerySpec but didn't like that I then lost linq and I have a lot of existing code written in linq.

In case this wasn't known : you can use them together in your app, you don't have to choose one over the other; i.e. it can help to formulate some queries in queryspec and others in linq.

The other thing is that I am programmatically regenerating the entire llbl library externally calling CliSyncWithDatabase and CliGenerator and I haven't found a way to get it to create just entities and not typed views when refreshing with the db. I end up with both and then my dll size is 10mb. I can either end up with just typed views or both. Haven't found a way to get just entities without going in by hand and doing things myself. So before a release, I try to remember to go in and delete all the typed views that I'm not using.

I don't think 10MB would be harmful, so I'd not worry about it at all.

I have a large db and i tested dll size between all entities and all typed views and it was 5mb (entities) vs 4mb (typed views) or 10mb for both. I had thought the typed views would be smaller.

It generates a poco class per typed view (if you selected that as output type for typed views of course), so that's pretty minimal.

Trying to get my webservice to restart even faster than it currently does. Plus, the regeneration takes that much longer when doing both. Once an llbl library has a table or view mapped as an Entity, what purpose does having it also mapped to a TypedView then serve? Is that just for performance and it's up to the dev to pick the right one for the moment? Or is it that if you need to read/write map to entity and if just read, use typedview?

Entities are for CUD processing, so create, update and delete (and to do that, read), so read/write scenarios. If you want to fetch data in a readonly fashion you can do that e.g. through derived models (which are designed to be used in services, so you can denormalize your data for readonly scenarios), typed views, typed lists or hand-rolled poco's if you want. You can also fetch entities of course, the performance is a bit slower (typedview pocos with queryspec vs. Entities )

I've switched back to entities and now using ResultsetFields and DataProjectorToCustomClass and so far so good! Thank you!

Performance wise where would building queries via entities and predicates but then projecting into custom poco slot into the performance chart at the below link?

https://weblogs.asp.net/fbouma/net-micro-orm-fetch-benchmark-results-and-the-fine-details

happyfirst wrote:

I've switched back to entities and now using ResultsetFields and DataProjectorToCustomClass and so far so good! Thank you!

Though that's pretty low level and requires a lot of typing. You could let the projector figure out the projection for you based on the type: https://www.llblgen.com/Documentation/5.8/LLBLGen%20Pro%20RTF/Using%20the%20generated%20code/QuerySpec/gencode_queryspec_projections.htm#easily-create-typed-projections

Performance wise where would building queries via entities and predicates but then projecting into custom poco slot into the performance chart at the below link? https://weblogs.asp.net/fbouma/net-micro-orm-fetch-benchmark-results-and-the-fine-details

Pretty high on the left, see my previous post: https://github.com/FransBouma/RawDataAccessBencher/blob/master/Results/20201112_net5_ef5.txt#L57 (it's equivalent to that)