Dapper Query() returned multiple same column

Question

I'm using dapper Query method to fetch a set of data from sqlite with left join, it does return the column i need but multiple times.

i tried these query on DB Browser it works fine,

 string sql =
         @"select a.id, a.alert_tag, lyr.layer_name, ln.line_name, t.task_name from alert_tag a
              LEFT JOIN layer_group lyr on lyr.layerID = a.layer_group
              LEFT JOIN line ln on ln.lineID = a.line
              LEFT JOIN task t on t.taskID = a.task";

then i call it,

using (IDbConnection cnn = new SQLiteConnection(Tools.LoadConnectionString()))
{
    var output = cnn.Query<dynamic>(sql);
    return output.ToList();
}

List<dynamic> Alerttag_List = new List<dynamic>();

private void LoadDGVdata()
{
     Alerttag_List = SqliteQuery_AlertTagModel.Load();
     dgv_AlertTag.DataSource = Alerttag_List;
}

I expected to get result like these

| id | alert_tag | layer_name | line_name | task_name |

but i got these

| id | alert_tag | layer_name | line_name | task_name | id | alert_tag | layer_name | line_name | task_name |

What happens when you run the query directly against the database — Chetan, May 15 '19 at 10:37
This sounds like a problem with the grid binding, to be honest. Can you do: `var propCount = TypeDescriptor.GetProperties(Alerttag_List[0]).Count` and tell me what it says? is it 5, or is it 10? — Marc Gravell, May 15 '19 at 10:47
also: how many rows are there in this example? is it 2 perhaps? I wonder whether the grid is generating a column per unique `PropertyDescriptor` reference, but the `RowBoundPropertyDescriptor` that dapper is exposing here is transient (generated on the fly per row). Also: what kind of grid is that? i.e. is this `DataGridView` ? or...? And: does this perhaps *gain* columns, i.e. you have `AutoGenerateColumns` (or whatever) enabled, and it is adding them whenever you change the source? — Marc Gravell, May 15 '19 at 10:50
Side note: generating a POCO class and mapping to *that* (i.e. `Query`) should be very reliable - the dynamic property API via `PropertyDescriptor` however, is... twitchy — Marc Gravell, May 15 '19 at 10:52
@MarcGravell it counted 10, i tried add another row it still shows 10 column, and i tried Query but it shows rows with blank cells in my grid. — espifi059 espifi059, May 15 '19 at 10:54
its `DataGridView`, now i try not to use dapper, but use `SQLiteCommand > SQLiteDataReader`, it works only shows me 5 column. but dont know what is the cause of using dapper and returned 10 cols. — espifi059 espifi059, May 15 '19 at 11:10
@espifi059espifi059 I just want to be 100% clear: `TypeDescriptor.GetProperties(Alerttag_List[0]).Count` returned 10? because if so, yeah, that's very interesting and that gives me somewhere to start — Marc Gravell, May 15 '19 at 12:09
it returned 10 when i use dapper's `Query<>`, and returned 5 when i use `SQLiteCommand` — espifi059 espifi059, May 16 '19 at 02:23
You are probably looking for the "splitOn" command. If that correct, please consider removing this question. (as it would be a duplicate) see : https://stackoverflow.com/questions/7472088/correct-use-of-multimapping-in-dapper — granadaCoder, Aug 14 '20 at 17:29

Chandra Shakar · Answer 1 · 2020-08-14T19:35:04.213

1

You can use: ExecuteReader. it will return single data. Example:

public dynamic GetTableData(string schemaName, string tableName)
        {
            string sql = $@"select * from {schemaName}.{tableName}";
            var tableData = _sqlConnection.ExecuteReader(sql);
            return tableData;
        }

edited Aug 14 '20 at 19:35

answered Aug 14 '20 at 17:20

Chandra Shakar

126
2
8

ExecuteReader is not compatible with `dynamic` AFAIK (context: I wrote it). And even if it was, I don't think that is the problem here. – Marc Gravell Aug 14 '20 at 17:56
ExecuteReader from dapper. I have already tested. and edited the answer with a picture. – Chandra Shakar Aug 14 '20 at 19:32
Hello Marc, Maybe same thing but for bindable grid/treelist problem happens. https://stackoverflow.com/questions/66096906/how-to-return-unique-columns-only-with-join-statement-in-dapper-query-sql – deveton Feb 08 '21 at 06:44

Dapper Query() returned multiple same column

1 Answers1