Is it possible to build query programmically from table data
Posted: Tue 24 Aug 2010 10:49
Slowly building my Linq knowledge.
I have an app with gridviews. Some columns are visible and some are not. Users are allowed to choose what they want.
ll is fine here.
I also allow the user to create their own fields.
I would like to know if it is possible using EF & Link to build a query that takes account of these user defined fields. Here is a cut down version of the important info
E.g. Structure
TblMaster (the main table).
IDMaster - Unique reference
Other fields...
TblUDFieldDefinition
IDFieldDef - Unique reference
Label - used in dynamic forms to associate the data with
Other fields ...
example data might be:
IDFieldDef = 1, Label = "Question to ask at next call"
IDFieldDef = 2, Label = "Number of support staff"
TblUDFieldHead //the actually stored data
IDFieldHead - Unique Reference
IDFieldDef - FK linking to TblUDFieldDefinition
IDMaster - Index (used when writing query to TblMaster - there is a reason why this is not a constraint in the full version)
Other fields
An example of
At the moment the UDFields are embedded into the program
And the data extraction for the viewgrid is as follows:
var query = from master in entity.TblMaster
let leftOuter = (from udfField in entity.TblUDFieldHead
where master.IDMaster == udfField.IDMaster
&& udfField.TblUDFieldDefinition.OIDUDFieldDefinition == 1
select new
{
udfField.theString
}).FirstOrDefault()
let leftOuter2 = (from udfField in entity.TblUDFieldHead
where master.IDMaster == udfField.IDMaster
& udfField2.TblUDFieldDefinition.OIDUDFieldDefinition == 2
select new
{
udfField.theDecimal
}).FirstOrDefault()
select new
{
OID = master.IDMaster,
Question = leftOuter.theString,
SupportStaff = leftOuter2.theDecimal,
};
result = SetResult(query);
The above works well but I am looking to seek a solution without writing raw SQL commands.
Using the above infrastructure as the example can I dynamically build a let leftouter statment for each of the rows in TblUDFieldDefinition. In reality it also contains a "type" column that I use to know if it is the decimal, string, date or boolean column to use as its value.
I then need to create a select new { ... } where ... is made up of static fields and however many leftouter constructs have been made.
I know I can create a string var and build my own SQL statements, but I am wondering if there is a "better"/"preferred" way with the system.
I am using Devart build 152 with postgreSQL & .NET 3.5 (not upgraded to VS2010 yet so .NET4 is not available to me).
Thanks
I have an app with gridviews. Some columns are visible and some are not. Users are allowed to choose what they want.
ll is fine here.
I also allow the user to create their own fields.
I would like to know if it is possible using EF & Link to build a query that takes account of these user defined fields. Here is a cut down version of the important info
E.g. Structure
TblMaster (the main table).
IDMaster - Unique reference
Other fields...
TblUDFieldDefinition
IDFieldDef - Unique reference
Label - used in dynamic forms to associate the data with
Other fields ...
example data might be:
IDFieldDef = 1, Label = "Question to ask at next call"
IDFieldDef = 2, Label = "Number of support staff"
TblUDFieldHead //the actually stored data
IDFieldHead - Unique Reference
IDFieldDef - FK linking to TblUDFieldDefinition
IDMaster - Index (used when writing query to TblMaster - there is a reason why this is not a constraint in the full version)
Other fields
An example of
At the moment the UDFields are embedded into the program
And the data extraction for the viewgrid is as follows:
var query = from master in entity.TblMaster
let leftOuter = (from udfField in entity.TblUDFieldHead
where master.IDMaster == udfField.IDMaster
&& udfField.TblUDFieldDefinition.OIDUDFieldDefinition == 1
select new
{
udfField.theString
}).FirstOrDefault()
let leftOuter2 = (from udfField in entity.TblUDFieldHead
where master.IDMaster == udfField.IDMaster
& udfField2.TblUDFieldDefinition.OIDUDFieldDefinition == 2
select new
{
udfField.theDecimal
}).FirstOrDefault()
select new
{
OID = master.IDMaster,
Question = leftOuter.theString,
SupportStaff = leftOuter2.theDecimal,
};
result = SetResult(query);
The above works well but I am looking to seek a solution without writing raw SQL commands.
Using the above infrastructure as the example can I dynamically build a let leftouter statment for each of the rows in TblUDFieldDefinition. In reality it also contains a "type" column that I use to know if it is the decimal, string, date or boolean column to use as its value.
I then need to create a select new { ... } where ... is made up of static fields and however many leftouter constructs have been made.
I know I can create a string var and build my own SQL statements, but I am wondering if there is a "better"/"preferred" way with the system.
I am using Devart build 152 with postgreSQL & .NET 3.5 (not upgraded to VS2010 yet so .NET4 is not available to me).
Thanks