Not sure if I should have split this into two tickets or not.
As described at the bottom, I’m trying to query for Parent + ‘First/Easliest’ Child in Ormlite. Because of the problems I had constructing the query in static typing (my attempt at bottom), I decided to instead use a raw SQL query, where the ‘complex query’ is of the form SELECT WHERE IDS IN (@IDS) has IDS as an array of GUIDS.
Everytime I attempt this however, the query fails , unable to parse char string to guid. When I attempt a similar query using WHERE IN @MyARR_OF_STRINGS, i get no results at all…
It’s as though passing lists of values as the @InIds param is being skipped, and I am strugging to understand why.
See this code and the descriptions of which queries work / return nothing unexpectedly / fail completely:
using System;
using ServiceStack;
using ServiceStack.Data;
using ServiceStack.DataAnnotations;
using ServiceStack.OrmLite;
using ServiceStack.OrmLite.SqlServer;
namespace ConsoleApp1
{
internal class Program
{
private static void Main(string[] args)
{
var connectionString = "Server=localhost;Database=mydb;User Id=sa; Password=mypass;";
IDbConnectionFactory fac =
new OrmLiteConnectionFactory(connectionString, SqlServer2012OrmLiteDialectProvider.Instance);
//using (var captured = new CaptureSqlFilter())
using (var db = fac.OpenDbConnection())
{
db.DropTable<FileTbl>();
db.CreateTable<FileTbl>();
db.Insert(new FileTbl { FileId = Guid.NewGuid(), Name = "a" });
db.Insert(new FileTbl { FileId = Guid.NewGuid(), Name = "b" });
db.Insert(new FileTbl { FileId = Guid.NewGuid(), Name = "c" });
var files = db.Select<FileTbl>();
var fileIds = files.Map(f => f.FileId);
var fileNames = files.Map(f => f.Name);
// First off, Regular select with no params works...
var thisSeemsToWorkFine_ResultsAreReturned = db.Select<FileTbl>("select * from FileTbl");
/*
// This crashes "System.Data.SqlClient.SqlException: 'Incorrect syntax near '@FileNames'.'
var listByTextWithboutBracesSeemsToFail = db.SqlList<FileTbl>("select * from [FileTbl] where Name in @FileNames ",
new { FileNames = fileNames });
*/
var listByTextWithBracesGivesNoResultsEvenThoughIThinkItShould = db.SqlList<FileTbl>(
"select * from [FileTbl] where Name in (@FileNames) ",
new { FileNames = fileNames });
var selectByTextGivesNoResultsEvenThoughIThinkItShould = db.Select<FileTbl>(
"select * from [FileTbl] where Name in (@FileNames) ",
new { FileNames = fileNames });
/*
// This crashes with: System.Data.SqlClient.SqlException: 'Conversion failed when converting from a character string to uniqueidentifier.'
var byGuids = db.SqlList<FileTbl>("select * from [FileTbl] where FileId in (@FileIds) ", new { FileIds = fileIds });
//captured.SqlStatements.Last().PrintDump();
*/
}
}
}
public class FileTbl
{
[PrimaryKey]
public Guid FileId { get; set; }
public string Name { get; set; }
public string Extension { get; set; }
public long FileSizeBytes { get; set; }
public bool IsDeleted { get; set; }
[RowVersion]
public ulong RowVersion { get; set; }
}
}
The motivating problem for all of this is:
I have been trying without success to use Ormlite to build a typed query for Event + EventSession where EventSession is the first scheduled session for each event (e.g. I select a list of events, and then try to also load up the EventSession where Min(StartDate) is the record selected, using a groupby.
The SQL would look like this:
select ES.*
FROM EventSession ES
INNER JOIN
(
select ES.EventId, MIN(StartDtUtc) as EarliestStartUtc
from EventSession ES
WHERE
ES.EventId in (@EventIds)
AND ES.IsDeleted = 0
Group By ES.EventId
) AS GRP
ON ES.EventId = GRP.EventId AND ES.StartDtUtc = GRP.EarliestStartUtc
So what I am trying in OrmLite is something like:
// get the list of events we are interested in by categoryid
var eventRows = Db.Select<EventTbl>(e => e.EventCategoryId == eventCategoryId);
var eventIds = eventRows.Map(e => e.EventId);
// now find the first event session start date for each event....
var earliestSessionsQuery = Db.From<EventSessionTbl>()
.Where(sd => eventIds.Contains(sd.EventId))
.GroupBy(sd => sd.EventId)
.Select(x => new { x.EventId, StartDate = Sql.Min(nameof(EventSessionTbl.StartDtUtc))})
// Now .. somehow... join that to the full EventSession record so that
// I can get ALL the event session columns (rather than only the eventid+min_date that i could query
// during the GROUP BY statement??
// But I can't make this work using static typing.. and as mentioned, the custom raw sql WHERE IN statement fails too...
thanks in advance, and for your patience.