The query returns System.InvalidOperationException: 'variable 'ana' of type 'LabReboot.ServiceModel.DBO.LRAAnalisi' referenced from scope '', but it is not defined'.
Do OrmLite supports this kind of projection? Or do I need to compose the query differently?
The AnalisiFlat DTO is defined as:
public sealed class AnalisiFlat
{
public LRAAnalisi Analisi { get; set; }
public LRAContenitore Contenitore { get; set; }
public LRARichiesta Richiesta { get; set; }
public LRAPaziente Paziente { get; set; }
}
No you can’t select whole tables in an anonymous type like that which isn’t available outside the scope of the SQL Expression. Whatever you select needs to be mapped into the type you’re selecting into. This answer explains how resultsets are mapped and different ways to access custom select queries.
I don’t know what List to List means, but you have to specify what tables you want to select in the SelectMulti<Table1,Table2,etc> API. But you don’t need the Select Expression in your query.
Ok I’ve solved that problem using SelectMulti and manually mapping all the types to my POCO Object.
I have a similar question now. In EF I can easily map result to a complex POCO like this:
var query = from doc in ctx.Set<Docs>()
where doc.something == 1
select new ComplexPoco
{
SimpleInt = doc.AnInteger,
RowCollection = (from rows in ctx.Set<Rows>()
where rows.DocumentId == doc.Id).ToList()
}
return query.ToList()
How can I obtain the same thing in OrmLite, without using any methods that involve N+1 queries?
This query is similar to my first one, with the only difference this time that I’m not selecting directly the entire tables, but I’m reading the fields of the tables. This example is present in the documentation, but I still get System.InvalidOperationException: 'variable 'ana' of type 'LabReboot.ServiceModel.DBO.LRAAnalisi' referenced from scope '', but it is not defined'.
In this link you already provided, you show the exact same way of building the query.
How can I choose which columns the query should return? I need to limit the size of the resultset to only the fields I need.
You can find the definitions of the 4 tables here, but I can already confirm you those tables are already in use with OrmLite, but they fail in those kind of Select<T1, T2,…>()
Any updates? I am currently blocked on this error. I need that Select() built like that since I have to limit the resultset coming from the DB. Any solution that involves selecting all the columns and iterating through the resultset (like you suggested at #2) is not applicable.
These tables have dependencies to missing classes, namespaces, etc… In order to be able to run/debug this code I need a stand-alone example I can run. These tables don’t exist in OrmLite and it’s extremely painful trying to pull out classes so I can actually get something to build.
So I’ve taken out the tables you’ve posted removed all the [Api*] attributes with constants to missing classes which don’t have any impact on OrmLite and uncommented all the properties used in the query in LRAIssues.cs and the query you’ve posted runs without issue. Please update LRAIssues.cs and add a test that causes this issue.
Projecting to an anonymous type works, projecting to my DTO doesn’t.
TatDto definition is:
public sealed class TatDto
{
public TatDto() { }
// identificazione
public int AnalisiId { get; set; }
public int ContenitoreId { get; set; }
public int RichiestaId { get; set; }
// date per differenze
public DateTime DataAccettazioneRichiesta { get; set; }
public DateTime? DataPrelievoContenitore { get; set; }
public DateTime? DataCheckinContenitore { get; set; }
public DateTime? DataEsecuzioneAnalisi { get; set; }
public DateTime? DataValidazioneAnalisi { get; set; }
// informazioni per regole
public int SessoPaziente { get; set; }
public DateTime? DataDiNascitaPaziente { get; set; }
public int? EtaPazienteRichiesta { get; set; }
public DateTime DataOraAccettazioneRichiesta { get; set; }
public int UnitaMisuraEtaPaziente { get; set; }
public int? SettimaneGravidanza { get; set; }
public int? RepartoId { get; set; }
public int PrioritaRichiesta { get; set; }
public int? LaboratorioRichiedente { get; set; }
public int? PrioritaContenitore { get; set; }
public int? IdLaboratorioEsecutoreCandidatoAnalisi { get; set; }
}
The LRAIssues.cs test I linked to derived from your code sample passes. I don’t have access to your source code to be able to repro any issues from these partial fragments. If you want to report an issue with your tables, either update the existing Test now in OrmLite or provide a stand-alone repro on GitHub I can run locally to repro the issue.
You need to select an anonymous type of only the fields you want to select. Don’t select a Typed class, if you want to populate results into a Typed DTO it needs to be on the call-site, e.g:
q.Select(new { .... });
var results = db.Select<IntoType>(q);
So your documentation is clearly wrong. Nowhere in the documentation it’s mentioned that an anonymous type is needed for a similar projection.
The 2 approach I have posted are exactly the same, with the only difference found in the typed class projection vs an anonymous one. You are promoting a less type-safe method, so this is an issue. There is absolutely no reasons to differenciate those 2 behaviours in the context I showed to you (besides technical reasons which somewhat may impact ormlite and not other orms like EF).
EDIT: we can safely say that the table definition is not the issue here, so you can remove LRAIssues.cs test since the issue resides in the typed projection.
What I’m trying to say is that you should just also allow a typed projection. It’s safer, it’s clearer and I don’t know any side effects it could have, while the anonymous projection (while very useful) doesn’t protect me from errors I may do.