Print Page | Close Window

How can I dynamically specify a list of columns to select in a query?

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2009
Forum Discription: For .NET 3.5
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=1520
Printed Date: 08-Apr-2025 at 8:31pm


Topic: How can I dynamically specify a list of columns to select in a query?
Posted By: AdamC
Subject: How can I dynamically specify a list of columns to select in a query?
Date Posted: 15-Oct-2009 at 8:40am
My application needs to provide users with a way to select one or more columns from a table at runtime.  In ADO.NET, I would simply build the SQL string on the fly.  For example, I would build a string that looks something like:
string sql = "SELECT " + columnList + " FROM myTable";
However, dynamically adding a list of columns to be selected in LINQ/ DevForce EF appears to be much more challenging.

I have spent quite a bit of time searching the web for ways to dynamically specify columns at runtime using LINQ (with not much success).  I see two possible solutions using DevForce.  I looked into PassthruEsqlQuery, but I couldn't determine a way to dynamically provide a list of columns in the .Select(...) portion of the query.  I am also looking into the AdoHelper class (used successfully by the IDGenerator class) so that I can build an ADO.NET SQL string dynamically.  I don't like moving away from LINQ, but this may be the only way to dynamically specify the columns I want to return from a table.  However, I am running into an issue using the AdoHelper approach.

In the first screen shot, you can see a working piece of code in our IDGeneerator class.  The dataSourceKey variable is passed by IdeaBlade as an argument and implements IDataSourceKey.  In the second screen shot, you can see a piece of code that does not work.  In that case dataSourceKey is not passed as an argument and I have to retrieve the dataSourceKey using the GetDataSourceKey() method.  Unfortunately the following line:

IdeaBlade.EntityModel.Edm.EdmKey edmKey = dataSourceKey as IdeaBlade.EntityModel.Edm.EdmKey;

sets the edmKey to null and I get a nullreference exception.  This exception does not occur in the IDGenerator class.  My guess is that GetDataSourceKey() is returning a dataSourceKey of type ClientEdmKey instead of EdmKey. 

So I have two questions:

1) Any suggestions on how I can get the AdoHelper approach to work outside of the IDGenerator class?
2) Any other ideas to select columns dynamically at runtime?

Before someone suggests just selecting all of the columns at once and then hiding the columns I don't want to display, keep in mind the table is a denormalized, warehouse-like table with about 1,000 columns.  I would prefer to return only the columns I need to avoid a performance hit due to large amounts of unneeded data returned over the wire. :-)

Thanks in advance for any information you can provide!

Screen shots

Code in my IDGenerator class (works).  Variable named dataSourceKey is of type IdeaBlade.EntityModel.Edm.EdmKey in this case:



Code in my business logic (does not work).  Variable named dataSourceKey is of type IdeaBlade.EntityModel.ClientEdmKey in this case:


Replies:
Posted By: kimj
Date Posted: 15-Oct-2009 at 6:26pm
As for problem #1, cast to a ClientEdmKey instead, and then create an EdmKey from that instead.   The ClientEdmKey is the client-side version of the EdmKey, and is what will be returned by the GetDataSourceKey() call.  DevForce will usually automatically coerce the ClientEdmKey to an EdmKey when it needs to, but since you need access to the RdbKey you have to do this yourself:
 
   var key = em.DataSourceResolver.GetDataSourceKey("Default");
   var clientkey = key as ClientEdmKey;
   var edmkey = new IdeaBlade.EntityModel.Edm.EdmKey(clientkey);
 
Problem #2 is interesting.  We don't currently offer a way to dynamically build the selected properties at run time, but it should be possible.  Search the web for "dynamic linq query" -- MS has a dynamic library sample, and if you want to get your hands dirty with LINQ expressions this should be doable in DevForce.   Before the next release (v5.2.4) I'll see if we can come up with any guidance or samples on how to do this.

Posted By: AdamC
Date Posted: 19-Oct-2009 at 10:41am
The ADO helper code snippet you provided worked!  Thanks so much Kim.

A few weeks ago I found a dynamic library from Microsoft that seemed promising (see http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx - http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx ).  Unfortunately it didn't seem to work with EF or I was doing something wrong.  I'm working on an extremely time-sensitive project and I didn't have much time to play.  Fortunately the ADO helper was able to come to the rescue!

Thanks again Kim and I look forward to any guidance IdeaBlade can provide. 


Print Page | Close Window