- Home
- LLBLGen Pro
- LLBLGen Pro Runtime Framework
Problem using FetchScalar
Joined: 30-Jun-2008
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
Joined: 28-Nov-2005
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()));
Joined: 17-Aug-2003
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);
Joined: 30-Jun-2008
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