Insert into... select

sviluppo1 · December 4, 2018, 9:47am

Hi mythz, i still have a problem with your implementation of InsertIntoSelect.

Specifically I get this exception:

System.ArgumentException: Field 'citaPaziente' not found in 'LRADATITAT'
  at in ServiceStack.OrmLite.ModelDefinition.GetOrderedFieldDefinitions(ICollection`1 fieldNames, Func`2 sanitizeFieldName)
  at in ServiceStack.OrmLite.OrmLiteDialectProviderBase`1.GetInsertFieldDefinitions(ModelDefinition modelDef, ICollection`1 insertFields)
  at in ServiceStack.OrmLite.SqlServer.SqlServerOrmLiteDialectProvider.PrepareParameterizedInsertStatement[T](IDbCommand cmd, ICollection`1 insertFields)
  at in ServiceStack.OrmLite.OrmLiteWriteCommandExtensions.InsertIntoSelect[T](IDbCommand dbCmd, ISqlExpression query, Action`1 commandFilter)
  at in ServiceStack.OrmLite.OrmLiteWriteApi.<>c__DisplayClass8_0`1.<InsertIntoSelect>b__0(IDbCommand dbCmd)
  at in ServiceStack.OrmLite.OrmLiteExecFilter.Exec[T](IDbConnection dbConn, Func`2 filter)
  at in ServiceStack.OrmLite.OrmLiteReadExpressionsApi.Exec[T](IDbConnection dbConn, Func`2 filter)
  at in ServiceStack.OrmLite.OrmLiteWriteApi.InsertIntoSelect[T](IDbConnection dbConn, ISqlExpression query)
  at in LabReboot.Repository.Repositories.ADatiTATRepo.<SetRecordsFromQuery>d__11.MoveNext() in C:\Git\LabReboot\LabReboot\LabReboot.Repositories\Repositories\ADatiTATRepo.cs:riga 184

It’s telling me that the field citaPaziente does not exists, in fact, it doesn’t. The field it is probably referring to is DataDiNascitaPaziente.

I’ve traced back the possible cause of this error here:

github.com

ServiceStack/ServiceStack.OrmLite/blob/a92aebff07d08a7dfad562ab48bb9a7512538813/src/ServiceStack.OrmLite/OrmLiteWriteCommandExtensions.cs#L702


internal static long InsertIntoSelect<T>(this IDbCommand dbCmd, ISqlExpression query, Action<IDbCommand> commandFilter)
{
    var dialectProvider = dbCmd.GetDialectProvider();


    var sql = query.ToSelectStatement();
    var selectFields = query.GetUntypedSqlExpression()
        .SelectExpression
        .Substring("SELECT ".Length)
        .ParseCommands();


    var fieldsOrAliases = selectFields
        .Map(x => x.Original.ToString().LastRightPart("AS").Trim().StripQuotes());
    
    dialectProvider.PrepareParameterizedInsertStatement<T>(dbCmd, insertFields: fieldsOrAliases);


    dbCmd.SetParameters(query.Params);


    dbCmd.CommandText = dbCmd.CommandText.LeftPart(")") + ")\n" + sql;


    commandFilter?.Invoke(dbCmd); //dbCmd.OnConflictInsert() needs to be applied before last insert id

The original query:

_db.From<LRAAnalisi>()
    .Join<LRAAnalisi, LRAContenitore>((ana, cont) => ana.ContenitoreId == cont.Id)
    .Join<LRAContenitore, LRARichiesta>((cont, ric) => cont.RichiestaId == ric.Id)
    .Join<LRARichiesta, LRAPaziente>((ric, paz) => ric.PazienteId == paz.Id)
    .LeftJoin<LRAAnalisi, LRAListaDiLavoro>((ana, lista) => lista.AnalisiId == ana.Id)
    .LeftJoin<LRAListaDiLavoro, LRDListaDiLavoro>((aListaLav, dListaLav) => aListaLav.ListaDiLavoroId == dListaLav.Id)
    .LeftJoin<LRDListaDiLavoro, LRDDevice>((dListaLav, dDevice) => dListaLav.Id == dDevice.ListaDiLavoroId)
    .Where<LRARichiesta>(ric => ric.Archiviata == (int)SiNo.No)
    .SelectDistinct<LRAAnalisi, LRAContenitore, LRARichiesta, LRAPaziente, LRDDevice>((ana, cont, ric, paz, dev) =>
        new //LRADatiTAT // Non piace ad OrmLite
        {
            AnalisiId = ana.Id,
            ContenitoreId = cont.Id,
            RichiestaId = ric.Id,
            TipoContenitore = cont.ContenitoreId,
            TipoAnalisi = ana.AnalisiId,
            DataAccettazioneRichiesta = ric.DataOraAccettazione,
            DataPrelievoContenitore = cont.DataOraPrelievo,
            DataCheckinContenitore = cont.DataOraPrimoCheckin,
            DataEsecuzioneAnalisi = ana.DataOraEsecuzione,
            DataValidazioneAnalisi = ana.DataOraValidazione,
            SessoPaziente = paz.Sesso,
            DataDiNascitaPaziente = paz.DataDiNascita,
            EtaPazienteRichiesta = ric.EtaPaziente,
            UnitaMisuraEtaPaziente = ric.UnitaDiMisuraEtaPaziente,
            SettimaneGravidanza = ric.SettimaneGravidanza,
            RepartoId = ric.RepartoId,
            PrioritaRichiesta = ric.PrioritaId,
            LaboratorioRichiedente = ric.LaboratorioRichiedenteId,
            PrioritaContenitore = cont.PrioritaId,
            IdLaboratorioEsecutoreCandidatoAnalisi = ana.LaboratorioEsecutoreCandidatoId,
            AreaAnalitica = dev.AreaAnaliticaId
        });

Could it be because you are cutting on AS, and the field is named DataDiNascitaPaziente?

Thanks

mythz · December 4, 2018, 10:34am

Looks like it, should be resolved from this commit now available from v5.4.1 on MyGet.