Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Projection to string
 

Pages: 1
LLBLGen Pro Runtime Framework
Projection to string
Page:1/1 

  Print all messages in this thread  
Poster Message
jovball
User



Location:
USA
Joined on:
23-Jan-2005 19:53:47
Posted:
393 posts
# Posted on: 23-Mar-2018 02:58:44.  
In order to populate some drop down lists in our application, we normally have a key/value pair. However, there are some lists that use the same data for the both. A simple List<string> would work well for that.

I can populate them the hard way using QuerySpec but not the easy way. I also cannot use a simple projection from a stored procedure. (i'm guessing that I could go old-school back to value projectors but am not that desperate.)

I don't see this as a bug but it would be nice if the projections could handle this. Is this something that could be "fixed?"

Here are the code snippets. This works using the ToValue() type projection.
Code:

var qf = new QueryFactory();
                
            var query1 = qf.Create()
                 .Select(() => CategoryFields.CategoryName.ToValue<string>())
                 .OrderBy(CategoryFields.CategoryName.Ascending());

            List<string> queryList1;
            using (var dataAdapter = new DataAccessAdapter())
            {
                queryList1 = dataAdapter.FetchQuery<string>(query1);
            }

            Console.WriteLine($"\nDynamic query Product Categories; Row count: {queryList1.Count}");
            foreach (var s in queryList1)
            {
                Console.WriteLine(s);
            }


The next two snippets fail because "'System.String' does not have a default constructor."
Code:

            var qf = new QueryFactory();
            var query2 = qf.Create()
                    .Select<string>(CategoryFields.CategoryName)
                    .OrderBy(CategoryFields.CategoryName.Ascending());

            List<string> queryList2;
            using (var dataAdapter = new DataAccessAdapter())
            {
                queryList2 = dataAdapter.FetchQuery<string>(query2);
            }

            Console.WriteLine($"\nDynamic query Product Categories; Row count: {queryList2.Count}");
            foreach (var s in queryList2)
            {
                Console.WriteLine(s);
            }



Code:

            var query3 = RetrievalProcedures.GetCategoryListCallAsQuery();
            List<string> procList;
            using (var dataAdapter = new DataAccessAdapter())
            {
                procList = dataAdapter.FetchProjection<string>(query3);
            }

            Console.WriteLine($"\nStored Proc Product Categories; Row count: {procList.Count}\n");
            foreach (var s in procList)
            {
                Console.WriteLine(s);
            }
Joel Reinford
LLBLGenPro Version: 5.5.3
Templates: Adapter
Framework: .Net 4.6
Database: SQL Server 2012/2014, DB2 v10
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37605 posts
# Posted on: 23-Mar-2018 12:08:31.  
Hmm, you're right this isn't very friendly. The default fetch returns a List<object[]> which isn't really handy either as the arrays contain just one value.

The type specified in the method is seen as an object which properties have to be set to values from the resultset, but for scalars that's of course not what is meant.

I'll ponder abit how to solve this, likely by introducing a FetchScalarList or something like that, to avoid having the method trying to do things automatically and fail in some ambiguous cases.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
jovball
User



Location:
USA
Joined on:
23-Jan-2005 19:53:47
Posted:
393 posts
# Posted on: 23-Mar-2018 12:31:00.  
That sounds reasonable. It was probably obvious but I should've included the stored procedure definition in the original post. Fixing that now.

Let's not ask _why_ we would have a stored procedure like this, m'kay? Laugh

Code:

CREATE PROCEDURE [dbo].[CategoryList]
AS
SELECT
    c.CategoryName
FROM
    dbo.Categories AS c
ORDER BY
    CategoryName;
Joel Reinford
LLBLGenPro Version: 5.5.3
Templates: Adapter
Framework: .Net 4.6
Database: SQL Server 2012/2014, DB2 v10
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37605 posts
# Posted on: 23-Mar-2018 12:38:44.  
WinkLaugh

Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37605 posts
# Posted on: 26-Mar-2018 14:44:14.  
While looking into this, I recognized we needed DataProjectorToValueList<T> class, and that's already there, which means I can rewrite your failing query as:

Code:
var qf = new QueryFactory();
var query2 = qf.Create()
        .Select(CategoryFields.CategoryName)
        .OrderBy(CategoryFields.CategoryName.Ascending());

var queryList2 = new List<string>();
using (var dataAdapter = new DataAccessAdapter())
{
    dataAdapter.FetchAsProjection(query2, new DataProjectorToValueList<string>(queryList2);
}

Console.WriteLine($"\nDynamic query Product Categories; Row count: {queryList2.Count}");
foreach (var s in queryList2)
{
    Console.WriteLine(s);
}


In case of a stored proc, that's a different story, for stored proc results, you need a full projection which is a bit cumbersome for a single field:

Code:
var query3 = RetrievalProcedures.GetCategoryListCallAsQuery();
List<string> procList;
using (var dataAdapter = new DataAccessAdapter())
{
    var qf = new QueryFactory();
    var q = qf.Create().Select(()=>CategoryFields.Name.ToValue<string>());
    procList = dataAdapter.FetchQueryFromSource(q, query3);
}

Console.WriteLine($"\nStored Proc Product Categories; Row count: {procList.Count}\n");
foreach (var s in procList)
{
    Console.WriteLine(s);
}


It's not doable to handle both situations with a single method, as the first requires a query to be specified and the second requires an IRetrievalQuery object which is generated from the queryspec query in the first situation.

The first query, with queryspec, is IMHO doable with the current API and that setup and doesn't require a different method. The stored proc is a problematic one, and it fails at the same spot as a PlainSQL value list query.

For the stored proc & plain sql situation, we'll look into how to solve that as it should be possible to do that without a lot of trouble. As both use the same projection system internally, fixing one will fix the other as well.
Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
Otis
LLBLGen Pro Team



Location:
The Hague, The Netherlands
Joined on:
17-Aug-2003 18:00:36
Posted:
37605 posts
# Posted on: 26-Mar-2018 16:45:31.  
The FetchProjection<string>(storedprocretrievalquery) and FetchQuery<string>(plainSqlString) work now in v5.4.

We've implemented it as a silent conversion: if you specify a system type (string, or value type from the System namespace) the query is expected to be a scalar set projection and will simply return a set of values of the specified type. Otherwise it will create a projection to the type specified as before.


Frans Bouma
LLBLGen Pro / ORM Profiler Lead Developer | Blog | Twitter
 
Top
jovball
User



Location:
USA
Joined on:
23-Jan-2005 19:53:47
Posted:
393 posts
# Posted on: 26-Mar-2018 17:36:07.  
That's great. I'm looking forward to 5.4 already.
Regular Smiley
Joel Reinford
LLBLGenPro Version: 5.5.3
Templates: Adapter
Framework: .Net 4.6
Database: SQL Server 2012/2014, DB2 v10
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.