LLBLGen 5.3.3 .NET Core 2.0 SQL Server Adapter Template

I have the following code and am experiencing an exception on the return adapter.FetchScalar<>... line:

        
               /// <summary>
        /// Retrieves all group types under current OrganizationId
        /// </summary>
        /// <returns></returns>
        public IEnumerable<GroupTypeViewModel> GetGroupTypes()
        {
            var qf = new QueryFactory();
            var allGroupTypes = new EntityCollection<GroupTypeLookupEntity>();
            using (var adapter = new DataAccessAdapter())
            {
                var q = qf.GroupTypeLookup
                    .Where(GroupTypeLookupFields.OrganizationId == AppVariables.AppCache.OrganizationID)
                    .OrderBy(GroupTypeLookupFields.GroupTypeName | SortOperator.Ascending);
                adapter.FetchQuery(q, allGroupTypes);
            }
            var myGroupTypes = allGroupTypes.Select(myEntity => new GroupTypeViewModel()
            {
                GroupTypeID = myEntity.GroupTypeId,
                GroupTypeName = myEntity.GroupTypeName,
                IsInUse=GroupTypeIsInUse(myEntity.GroupTypeId)
            });


            return myGroupTypes;
        }
        /// <summary>
        /// True if GroupType is in use by a WorkflowTaskTemplate
        /// </summary>
        /// <param name="Id">GroupTypeId</param>
        /// <returns></returns>
        public bool GroupTypeIsInUse(Guid Id)
        {
            //int amount = 0;
            var qf = new QueryFactory();
            using (DataAccessAdapter adapter = new DataAccessAdapter())
            {
                var q = qf.Group
                    .Where(GroupFields.GroupTypeId == Id);
                return adapter.FetchScalar<int>(qf.Create().Select(q.CountRow())) > 0;
            }
        }

the exception is: "InvalidOperationException: The database returned a NULL value however the type specified ('System.Int32') isn't a nullable type." and detail is: SD.LLBLGen.Pro.ORMSupportClasses.FieldUtilities.ConvertScalarValueToType<TValue>(object valueToConvert) Workflow.Models.LLBLGen.LLBLGenLookupStorage.GroupTypeIsInUse(Guid Id) in LookupStorage.cs + return adapter.FetchScalar<int>(qf.Create().Select(q.CountRow())) > 0;

There is a 1:n relationship between GroupTypeLookup and Group. Also, the following code works:

        public bool GroupTypeIsInUse(Guid Id)
        {
            var qf = new QueryFactory();
            var allGroups = new EntityCollection<GroupEntity>();
            using (var adapter = new DataAccessAdapter())
            {
                var q = qf.Group
                    .Where(GroupFields.GroupTypeId == Id);
                adapter.FetchQuery(q, allGroups);
            }
            return allGroups.Count > 0;

        }

Any idea why I'm getting the exception?

Also, if there's a better way to do this, I'm all ears. It's my first time using adapter and the QueryFactor.

Thanks.

Laurie

I can't reproduce your error. I tried with and without results. Only difference is that I'm using .NET 4.5.2. I will try to reproduce it using .NET Core 2.

In the meantime you could replace this...

 var q = qf.Group
   .Where(GroupFields.GroupTypeId == Id);
return adapter.FetchScalar<int>(qf.Create().Select(q.CountRow())) > 0;

for this...

 var q = qf.Group
   .Where(GroupFields.GroupTypeId == Id);
return adapter.FetchScalar<int>(qf.Create().Select(q.Any()));

Can't reproduce it either on .net core 2.x:

[Test]
public void CountOnEmptySetShouldReturnZeroTest()
{
    using(var adapter = new DataAccessAdapter())
    {
        var qf = new QueryFactory();
        var q = qf.Order.Where(OrderFields.CustomerId == "DOESNTEXIST");
        Assert.AreEqual(0, adapter.FetchScalar<int>(qf.Create().Select(q.CountRow())));
    }
}

Generated Sql query: 
    Query: SELECT TOP(@p3) (SELECT COUNT(*) AS [LPAV_] FROM [Northwind].[dbo].[Orders] WHERE ( ( [Northwind].[dbo].[Orders].[CustomerID] = @p1))) AS [LLBLV_1] FROM [Northwind].[dbo].[Orders]
    Parameter: @p1 : StringFixedLength. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "DOESNTEXIST".
    Parameter: @p3 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1.

(edit) btw, you can also write both queries into one, which is way more efficient:

var qf = new QueryFactory();
var q1 = qf.GroupTypeLookup
          .Where(GroupTypeLookupFields.OrganizationId.Equal(AppVariables.AppCache.OrganizationID))
          .OrderBy(GroupTypeLookupFields.GroupTypeName.Ascending())
          .Select(GroupTypeLookupFields.GroupTypeId, GroupTypeLookupFields.GroupTypeName, 
                  qf.GroupFields.CorrelatedOver(GroupEntity.Relations.GroupTypeLookupEntityUsingGroupTypeId)
                    .Select(Functions.CountRow()).ToScalar().As("BaseCount"))
          .As("S");
var q = qf.Create()
          .Select(() => new GrouipTypeViewModel()
                        {
                            GroupTypeID = GroupTypeFields.GroupTypeID.Source("S").ToValue<int>(),
                            GroupTypeName = GroupTypeFields.Name.Source("S").ToValue<string>(),
                            IsInUse = Functions.IIF(qf.Field("S", "BaseCount").GreaterThan(0), true, false).ToValue<bool>()
                        })
          .From(q1);

var results = adapter.FetchQuery(q);

This avoids you doing an SELECT N+1 where you first query the entities and then per query you issue a query for the count. Here you have it in 1 query. I might have a detail wrong here and there but this should work.

(test on northwind)

var qf = new QueryFactory();
var q1 = qf.GroupTypeLookup
          .Where(GroupTypeLookupFields.OrganizationId.Equal(AppVariables.AppCache.OrganizationID))
          .OrderBy(GroupTypeLookupFields.GroupTypeName.Ascending())
          .Select(GroupTypeLookupFields.GroupTypeId, GroupTypeLookupFields.GroupTypeName, 
                  qf.GroupFields.CorrelatedOver(GroupEntity.Relations.GroupTypeLookupEntityUsingGroupTypeId)
                    .Select(Functions.CountRow()).ToScalar().As("BaseCount"))
          .As("S");
var q = qf.Create()
          .Select(() => new GrouipTypeViewModel()
                        {
                            GroupTypeID = GroupTypeFields.GroupTypeID.Source("S").ToValue<int>(),
                            GroupTypeName = GroupTypeFields.Name.Source("S").ToValue<string>(),
                            IsInUse = Functions.IIF(qf.Field("S", "BaseCount").GreaterThan(0), true, false).ToValue<bool>()
                        })
          .From(q1);

var results = adapter.FetchQuery(q);

Perfect! That was exactly what I was looking for. The code that worked is as follows:

        public IEnumerable<GroupTypeViewModel> GetGroupTypes()
        {
            using (var adapter = new DataAccessAdapter())
            {
                var qf = new QueryFactory();
                var q1 = qf.GroupTypeLookup
                         .Where(GroupTypeLookupFields.OrganizationId.Equal(AppVariables.AppCache.OrganizationID))
                         .OrderBy(GroupTypeLookupFields.GroupTypeName.Ascending())
                         .Select(GroupTypeLookupFields.GroupTypeId, GroupTypeLookupFields.GroupTypeName,
                                 qf.Group.CorrelatedOver(GroupEntity.Relations.GroupTypeLookupEntityUsingGroupTypeId)
                                    .Select(Functions.CountRow()).ToScalar().As("BaseCount"))
                         .As("S");
                var q = qf.Create()
                         .Select(() => new GroupTypeViewModel()
                         {
                             GroupTypeID = GroupTypeLookupFields.GroupTypeId.Source("S").ToValue<Guid>(),
                             GroupTypeName = GroupTypeLookupFields.GroupTypeName.Source("S").ToValue<string>(),
                             IsInUse = Functions.IIF(qf.Field("S", "BaseCount").GreaterThan(0), true, false).ToValue<bool>()
                         })
                         .From(q1);

                var results = adapter.FetchQuery(q);
                return results;
            }           
        }

You had qf.GroupFields.CorrelatedOver, which should have been qf.Group.CorrelatedOver

Thanks so much!

Laurie