Hi,
in table Menu I have 5 records and when selecting only some columns with this command:
var query = db.From<Entities.Menu>().Select(x => new { x.Id, x.RecId, x.Name });
var store = db.Select<(int id, Guid recId, string name)>(query);
…I get 5 empty records without values in returned List (screenshot is below).
I’m doing something wrong or is there a bug?
Thanx, Tomas
You likely have a mapping error, enable a debug logger to see if there were any issue, e.g:
LogManager.LogFactory = new ConsoleLogFactory(debugEnabled:true);
Unfortunately no error.
Only SELECT query is there (the second one is called by query above - the first one is called by db.Select<Entities.Menu>() for whole entity)
Can not be a problem with custom SqliteDialect.Provider.NamingStrategy?
Mapping “Id” to “id” and “RecId” to “rec_id” … “underscore separeted” 
What does the results look like when you query the generated SQL? Also please provide the class definition for Entities.Menu and what RDBMS are you using? Looks like it may be PostgreSQL.
And are you using any custom OrmLiteConfig or naming strategy?
Yes the custom naming strategy is likely the issue. What naming strategy are you using?
Here is code for naming strategy:
public class DatabaseNamingStrategy : OrmLiteNamingStrategyBase
{
public override string GetTableName(string name)
{
return ToUnderscoreSeparated(name);
}
public override string GetColumnName(string name)
{
return ToUnderscoreSeparated(name);
}
string ToUnderscoreSeparated(string name)
{
string r = char.ToLower(name[0]).ToString();
for (int i = 1; i < name.Length; i++)
{
char c = name[i];
if (char.IsUpper(name[i]))
{
r += "_";
r += char.ToLower(name[i]);
}
else
{
r += name[i];
}
}
return r;
}
}Looks like you could use OrmLite’s built-in LowercaseUnderscoreNamingStrategy:
Can you provide the class definition of Entities.Menu and a screenshot of running the query in a GUI (generated in the debug log) so it shows the returned resultset.
Database is SQLite.
Generated SQL is exactly this:
SELECT "id", "rec_id", "name"
FROM "menu"
And class definition:
public class Menu : EntityBase<Menu>
{
[PrimaryKey, AutoIncrement]
public int Id { get; set; }
[ForeignKey(typeof(Menu))]
public int? ParentId { get; set; }
[Required]
public MenuType Type { get; set; }
[Required, StringLength(100)]
public string Name { get; set; }
[StringLength(100)]
public string Form { get; set; }
[StringLength(50)]
public string Icon { get; set; }
[StringLength(1000)]
public string Style { get; set; }
[ForeignKey(typeof(User))]
int? UserId { get; set; }
}
And base class:
public abstract class EntityBase<T> : IEntity<T>
{
[Required, Default(typeof(bool), "0")]
public bool IsDeleted { get; set; }
[Required, Default(typeof(bool), "1")]
public bool IsActive { get; set; } = true;
public int? Position { get; set; }
//public ulong RowVersion { get; set; }
public Guid RecId { get; set; }
}Can you provide a view of the resultset returned when running the query, i.e. so we can see what data is meant to be populated.
Here is it:
Maybe I also have the question why is GUID stored in char (36) as “binary”?
I’m unable to repro this issue with the code provided in this commit, which passes in both .NET Framework and .NET Core.
[Test]
public void Can_select_dynamic_results_from_custom_NamingStrategy()
{
OrmLiteConfig.BeforeExecFilter = dbCmd => Console.WriteLine(dbCmd.GetDebugString());
var hold = SqliteDialect.Provider.NamingStrategy;
SqliteDialect.Provider.NamingStrategy = new DatabaseNamingStrategy();
using (var db = OpenDbConnection())
{
db.DropAndCreateTable<Menu>();
var rows = new[] {
new Menu {
Name = "Test List",
RecId = new Guid("2F96233B-152E-4D20-BE08-5633431A9EBC")
}
};
db.InsertAll(rows);
var q = db.From<Menu>().Select(x => new { x.Id, x.RecId, x.Name });
var results = db.Select<(int id, Guid recId, string name)>(q);
var expected = rows[0];
Assert.That(results[0].id, Is.EqualTo(1));
Assert.That(results[0].recId, Is.EqualTo(expected.RecId));
Assert.That(results[0].name, Is.EqualTo(expected.Name));
}
SqliteDialect.Provider.NamingStrategy = hold;
}
Which generates the following SQL:
SQL: CREATE TABLE "menu"
(
"id" INTEGER PRIMARY KEY AUTOINCREMENT,
"parent_id" INTEGER NULL,
"name" VARCHAR(100) NOT NULL,
"form" VARCHAR(100) NULL,
"icon" VARCHAR(50) NULL,
"style" VARCHAR(1000) NULL,
"user_id" INTEGER NULL,
"is_deleted" INTEGER NOT NULL DEFAULT (0),
"is_active" INTEGER NOT NULL DEFAULT (1),
"position" INTEGER NULL,
"rec_id" CHAR(36) NOT NULL,
CONSTRAINT "FK_menu_menu_ParentId" FOREIGN KEY ("parent_id") REFERENCES "menu" ("id")
);
SQL: INSERT INTO "menu" ("parent_id","name","form","icon","style","user_id","is_deleted","is_active","position","rec_id") VALUES (@ParentId,@Name,@Form,@Icon,@Style,@UserId,@IsDeleted,@IsActive,@Position,@RecId)
PARAMS: @ParentId=, @Name=Test List, @Form=, @Icon=, @Style=, @UserId=, @IsDeleted=0, @IsActive=1, @Position=, @RecId=2f96233b-152e-4d20-be08-5633431a9ebc
SQL: SELECT "id", "rec_id", "name"
FROM "menu"
The one change that was needed for OrmLite was making UserId public and removing the ForeignKey reference since no User class was provided but as it’s not apart of the query it wont have any effect on the test.
If you haven’t already please upgrade to the latest version of OrmLite, if it’s still an issue please upload a stand-alone repro on GitHub I can run locally to repro the issue.
It looks good and the code seems to be equally.
My version is 5.1.1
In my project the problem still exists. Look at this bellow - two same identical entities (Menu), first Select whole entities, second only some columns.
Result:
I’m assuming OrmLite was used to create the tables right?
If so, I’d need a small stand-alone repro in order to be able to investigate further. Please post a standalone repro to GitHub I can run locally to repro the issue.
