0

I'm implementing some generic solution to query IDs from database. All I know, is for example that given a table A, it has a column Id having an integer type. I don't know if it's uint32 or int64, etc.

I'd like to query the maximum of the ID using DbContext.Database.SqlQuery, however, I cannot make it work in a weakly typed manner. So I always have to provide a strong element type, which basically I wouldn't like as it might be int or long.

If I'm running it like .SqlQuery<object>, than I'm getting exception when trying to convert it to for example long using Convert.ToInt64, saying that System.Object is not convertible to that type. Seems like EF is kind of misunderstanding me or so.

Even when the actual underlying type is Int32, and I try to query for long, I get an exeption stating that I cannot convert Int32 to long, which completely does not make sense to me.

How could I achieve a weakly typed query result which I can just convert to whatever I want?

Zoltán Tamási
  • 12,249
  • 8
  • 65
  • 93
  • Can you cast it to varchar and then parse it in C# maybe? – Captain Kenpachi Oct 12 '20 at 15:22
  • `I'd like to query the maximum of the ID` why? Are you trying to use the buggy MAX+1 algorithm to generate new IDs? This risks generating duplicate IDs or worse, associating irrelevant records if the last records are deleted. Why not use EF's built-in identity generation mechanism? – Panagiotis Kanavos Oct 12 '20 at 15:22
  • Btw `SqlQuery` can work with any type, but if you only want to return a single value, you shouldn't be using an ORM. A plain ADO.NET query or eg Dapper could be used to execute that query. Just don't use MAX+1. You can specify alternate ID generation strategies in EF. If you want to know the IDs in advance, a far better solution would be to use a [SEQUENCE](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql?view=sql-server-ver15) – Panagiotis Kanavos Oct 12 '20 at 15:24
  • Just `.SqlQuery(...)` DB types are different from C# types anyway, so the important thing **for a query** is, to have a target type thats big enough to hold the result – grek40 Oct 12 '20 at 15:27
  • @grek40 Thanks for the comment, yes, this is why I'm suprised how `long` is not working for an `int`. – Zoltán Tamási Oct 12 '20 at 19:16
  • @PanagiotisKanavos Thanks for your comments, I am not planning to use is for ID generation of course :) Actually I'm working on kind of a generic database reset functionality for reliable and deterministic integration testing for databases having strictly monotonic IDs. Using EF is just for simplicity as we already have a reference to that. However, I'm thinking about going down to pure ADO, I know it's kind of trivial in that context. – Zoltán Tamási Oct 12 '20 at 19:17
  • Maybe you can re-phrase your question with more details as in "This is my table definition, this is my SQL query, I expected `SqlQuery` to work, why does it not work / how to make it work?" Currently, your question doesn't provide such a level of detail, so it's hard to reproduce your problem. – grek40 Oct 13 '20 at 16:09

1 Answers1

0

Unfortunately, Entity Framework is designed to map definite (strong typed) POCO classes.

The names of the types and each of the mapped properties must be equivalent.

  • You can convert, for example, long to int with those techniques. But that also requires you to know column types beforehand.
  • You can convert to dynamic type with codes shown here. But that is overkill compared with ADO.NET, IMO.
Han Zhao
  • 1,932
  • 7
  • 10