0

Which do you prefer and why?

db.Query<Student>("Select * from Students").ToList();

or

db.Query<Student>("Select Id, FirstName, LastName, Address, Age, Etc From Students").ToList();

Amit Joshi
  • 15,448
  • 21
  • 77
  • 141
NPearson
  • 141
  • 1
  • 6

1 Answers1

1

In terms of RDBMS
More the descriptive, better for RDBMS. This is because, RDBMS do not need to resolve the symbols.

So, Select Id,.... From Students is better. Because with this, RDBMS do not need to resolve the *.

Also, with this you may easily limit the columns you want to select. This is not a good comparison though because * vs selected columns might be different topic.

Further, Select Id,.... From dbo.Students is even better. This also tells RDBMS where does Students table belong; so even less work for RDBMS.

Not sure though, but I think, such descriptive queries help generating and reusing query execution plan in efficient way.

As you have not covered WHERE clause in your sample, I will not go into details there. Just to mention, parameterized queries are better choice.

In terms of Dapper
Dapper is O/R mapper. It sends the query you passed in to RDBMS without any change(really? anyway; different topic...). After receiving result from RDBMS, it simply maps the RDBMS specific data structure to your Entity.

There are many ways to make this mapping happen; please search Stack Overflow for more details. By default, it happens automatically if your column/property names and table/class names are same.

Hence, whether you do * or selected columns, does not matter to dapper.

Amit Joshi
  • 15,448
  • 21
  • 77
  • 141
  • "Hence, whether you do * or selected columns, does not matter to dapper." - right it doesn't matter to dapper. I guess the issue is if you specify the columns, it makes maintenance of the C# code more difficult since every time the table changes you have to edit the column list. On the flip side, if you don't specify the columns the database has to figure it out and like you said, more descriptive is better. So they each have a benefit, more descriptive queries vs ease of maintaining when columns change. Which is more important? – NPearson Apr 08 '21 at 07:03
  • 1
    I only attempted to answer in terms of RDBMS and Dapper. The third part you raised now, "maintainable code" was not part of your question. But yes; you catch it right. This is trade-off based on your specific use-case. If you want to use descriptive queries and also a maintainable code, you may consider full ORM like NH or EF which will generate such queries for you; but......but... Many but`s; different topic. – Amit Joshi Apr 08 '21 at 07:07
  • Well it was kind of part of the question. I was asking what people thought was a best practice and why, which leaves no reasons off the table. I definitely have a strong opinion on this but I ran into someone who has the opposite opinion, so I was looking for what the community thinks without giving all my reasons one way or the other that would be leading. Yeah and talking about the evils of EF is how we got on this discussion. – NPearson Apr 08 '21 at 10:07