Use LINQ to SQL Engine to create dynamic SQL

Question

I am working on an object relational mapper of sorts, which needs to dynamically create multi-table SQL statements at run time.

The database structure is only known at run time, which rules out using LINQ to SQL in its intended fashion.

However, I would like to tap into the LINQ to SQL query generation capabilities at run time if possible. Maybe this can be done by creating the "LINQ to SQL" data context at run time, and use the SQL query generation capabilities?

Alternatively, is there any other .NET libraries out there that will allow dynamic query creation from a database structure only known at run time?

Example of a generated query:

SELECT 
    T1.Name,
    T2.Category,
    T3.MainCategory
FROM products t1
LEFT JOIN categories t2
ON t2.CategoryId = t1.CategoryId
LEFT JOIN MainCategories t3
ON t3.MainCategoryId = t2.MainCategoryId
WHERE t3.Active = 1

ORDER BY t2.Category, t1.Name

My application already handles deciding which tables and fields are required, as well as filters and sorting, and this information is available at run time. I would rather translate this to another SCHEMA of sorts, like a DataContextDataClasses object, rather than building the SQL text manually.

Any advice or direction much appreciated.

Have you seen these two urls? dynamiclinq.codeplex.com and github.com/markrendle/Simple.Data — Ali Dehghan
– Ali Dehghan, Commented Apr 7, 2014 at 9:10
That second link (Simple.Data) looks very interesting... will look into it, thanks! — Paul Grimshaw
– Paul Grimshaw, Commented Apr 7, 2014 at 9:19
Generally, there are several open-source micro ORMs you can study and get some brilliant ideas. — Ali Dehghan
– Ali Dehghan, Commented Apr 7, 2014 at 9:23

Ali Dehghan · Accepted Answer · 2014-04-19 05:09:06Z

1

In case of dynamic generating concepts, usually micro-orms such as Simpe.Data can be useful and worth taking a look. Also, it should be mentioned that using dynamic mechanisms causes a great feature like IDE-Intellisence getting lost; but, a remarkable and dramatic performance gains instead which is considerable in terms of some applications.

answered Apr 19, 2014 at 5:09

Ali Dehghan

4623 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Use LINQ to SQL Engine to create dynamic SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related