Read object from c# database into classes directly

Question

What I am trying to do is read a database, row by row, and use the data from each row to initialize an object of the type that data represents. In this case I am reading rows of the Device table and trying to create Device objects with that data. I saw this SO link:

and I tried this snippet:

     using(var dc = new DataContext(connectionString)) 
     {
              List<Person> people = dc.ExecuteQuery(@"
              SELECT Id, Name Address
              FROM [People]
              WHERE [Name] = {0}", name).ToList(); // some LINQ too
     }

But it is telling me

The type arguments for this usage cannot be inferred from the usage

Is this in principal correct or should I be using the BondIO serializer/deserializer? as mentioned here

Also the order of the members in the object may not be the same as the order of the columns in the database, is this relevant?

Later that same day....

I now have a DBContext with all my database objects defined like this:

    public class MyContext : DBContext
    {
            public dbSet<Device>{ get; set;}
            etc...
    }

And I now try to get object using this snippet:

    using (var db = new MyContext(ConnectionString))
        {
            var res = db.Device.Find(ID);
        }

However this gives an exception message

Could not load type 'System.Data.Entity.ModelConfiguration.Conventions.AttributeToColumnAnnotationConvention`2

I have checked the database and it should return 1 value based on the PrimaryKey ID that I am passing. Anybody have any hints what I'm still doing wrong.

HGMamaci · Accepted Answer · 2017-08-30 09:21:38Z

1

You cannot, because ExecuteQuery is for executing statements, not for querying database. You should use SqlQuery instead What you can do is, to create a new class with the properties you want to set in your query, means a simplified version of your query. In your case

public class Device
{
  public int  Id {get;set} 
  public string  Name {get;set} 
  public string  Address {get;set}
}

then use it as

var people = dc.ExecuteQuery<Device>(@"
              SELECT Id, Name Address
              FROM [People]
              WHERE [Name] = {0}", name).ToList();

edited Aug 30, 2017 at 9:21

answered Aug 30, 2017 at 8:54

HGMamaci

1,40613 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Alan Over a year ago

But if I have the Device class already, with its members defined like you just have, why cant I query all columns and load them straight into the Device object?

HGMamaci Over a year ago

I am soory - My answer is wrong. I am correcting it.

Alan Over a year ago

Do you mean ExecuteCommand?? DataContext.SqlQuery doesnt seem to exist....

HGMamaci Over a year ago

I am sorry - I think I am totally mistaken. I was actually referring to DBContext of the Entity Framework. With DataContext it should be ExecuteQuery

Alan Over a year ago

There is definitely a easy solution to do this, I have seen it in a previous project I worked on, but I just cannot remember right now...Will answer this question when I find it

Collectives™ on Stack Overflow

Read object from c# database into classes directly

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related