Projection to string

Posts   
 
    
jovball
User
Posts: 434
Joined: 23-Jan-2005
# 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.


  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."


            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);
            }


            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);
            }

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# 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 | Lead developer LLBLGen Pro
jovball
User
Posts: 434
Joined: 23-Jan-2005
# 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? smile


CREATE PROCEDURE [dbo].[CategoryList]
AS 
SELECT 
    c.CategoryName
FROM 
    dbo.Categories AS c
ORDER BY 
    CategoryName;

Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# Posted on: 23-Mar-2018 12:38:44   

wink smile

Frans Bouma | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# 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:

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:

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 | Lead developer LLBLGen Pro
Otis avatar
Otis
LLBLGen Pro Team
Posts: 39588
Joined: 17-Aug-2003
# 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 | Lead developer LLBLGen Pro
jovball
User
Posts: 434
Joined: 23-Jan-2005
# Posted on: 26-Mar-2018 17:36:07   

That's great. I'm looking forward to 5.4 already. simple_smile