Is it possible to build query programmically from table data

[kerrywales]

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

[StanislavK]

The problem is that with such approach you will need to create objects of an anonymous class with variable number of properties (i.e., IDMaster, other TblMaster fields, and then an unknown number of user-defined fields). As this is not allowed in C#, we recommend you to
- configure the grid using the DataContext.Definitions collection;
- load Master entities together with the collection of their Heads;
- populate the grid using these Masters and Heads collections.

For example, if the tables used are defined as

Code: Select all

CREATE TABLE master
(
  id integer NOT NULL,
  CONSTRAINT master_pkey PRIMARY KEY (id)
);

CREATE TABLE definition
(
  def_id integer NOT NULL,
  title character varying,
  CONSTRAINT definition_pkey PRIMARY KEY (def_id)
);

CREATE TABLE head
(
  h_id integer NOT NULL,
  m_id integer NOT NULL,
  def_id integer NOT NULL,
  "content" character varying,
  CONSTRAINT head_pkey PRIMARY KEY (h_id),
  CONSTRAINT head_def FOREIGN KEY (def_id)
      REFERENCES definition (def_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT head_master FOREIGN KEY (m_id)
      REFERENCES master (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

you can create a dictionary based on Definitions (the dictionary will simplify retrieving of titles by identity numbers):

Code: Select all

Dictionary definitions = new Dictionary();
dc.Definitions.ToList().ForEach(d => definitions.Add(d.DefId, d));

With this dictionary, you can configure the grid columns. To retrieve data, please set the LoadOptions of your data context and simply retrieve the list of the Master instances:

Code: Select all

DataLoadOptions options = new DataLoadOptions();
options.LoadWith((Master m) => m.Heads);
dc.LoadOptions = options;
List masters = dc.Masters.ToList();

Now you are able to populate the grid using the master's own fields, and master.Heads[index].DefId and master.Heads[index].Content properties to fill the user-defined fields.

Please tell us if this helps.

[kerrywales]

Hello,
I haven't replied sooner as I have been trying differing techniques to get around the problem.
I have VS 2008 and .Net 3.5, so DataLoadOptions (as far as I can see) is not an option.

I have tried using the .Include without much success and .Load with similar issues.

Latest attempt was
var q1 = (from client in ReportDataConnection.TblClient
select client ).AsEnumerable().Take(noRows);

foreach (TblClient theClient in q1)
{
//For one to one relationships that have the "Reference" object
//this works fine
if (!theClient.TblOfficeReference.IsLoaded)
theClient.TblOfficeReference.Load();
..... more one-to-one's listed here
//For one to Many this construct fails in as much
// that it only returns the first record in TblJobs, not all
if (!theClient.TblJobs.IsLoaded)
theClient.TblJobs.Load();


I noticed a reference to using
MultipleActiveResultSets=True
in the connectionstring but the the runtime says that this option is not valid.

I also tried
var q1 = (from client in ReportDataConnection.TblClient
select client ).Take(noRows);

foreach (TblClient theClient in q1)
{
if (!theClient.TblOfficeReference.IsLoaded)
theClient.TblOfficeReference.Load();
..... more one-to-one's listed here
};
//Now get the one to Many's
var q2 = (from client in q1
from jobs in ReportDataConnection.TblJobs
where client.IDClient == jobs.TblClient.IDClient
select new { client, matter });
var q3 = (q2.AsEnumerable().Select(c => c.client)).ToList();

Now this one reported the correct number of children i.e if there were 4 jobs for client XXX then I would get 4 rows in the master detail report, except that each of the 4 children were the same record.

I also tried
var q1 = (from client in ReportDataConnection.TblClient
from jobs in ReportDataConnection.TblJobs
where client.IDClient == jobs.TblClient.IDClient
select client ).Take(noRows);
var q2 = (q1.AsEnumerable().Select(c => c.client)).ToList();
foreach (TblClient theClient in q2)
{
if (!theClient.TblOfficeReference.IsLoaded)
theClient.TblOfficeReference.Load();
..... more one-to-one's listed here
};
As I found a reference telling me it would return all the results.

I have tried other permutations as well but none to return the correct set of results.

[AndreyR]

The first approach worked for me in a simple example.
Could you please provide a full sample illustrating the problem?
Please include both table script and some test data.
Send the info to support * devart * com, subject "EF: Load"