Convert string columns to int properties

Question

I have a database that I cannot change the schema. I want to use EF read only to access the data, but almost all of the columns are of the wrong type. Integers are stored as packed decimals, Booleans are stored as T or F, etc. 

I have seen several posts about this problem, but no good solutions.

All I need to do is query and retrieve data in the correct format. What is the best approach?

Answer 1

Hi BrentKauffman,

>>All I need to do is query and retrieve data in the correct format. What is the best approach?

As your requirement, I would suggest that you could define a class like data transfer object (DTO), which you could convert the type and storage the records in this class as you want. Here is a sample for your reference.

original class

 public partial class Test
    {
        public int Id { get; set; }

        public decimal? Field1 { get; set; }

        [StringLength(1)]
        public string Field2 { get; set; }

        [StringLength(50)]
        public string Field3 { get; set; }
    }

Dto Class

  public class TestDto
    {
        public int Id { get; set; }
        public int IntField1 { get; set; }
        public bool BoolField { get; set; }
        public int IntField2 { get; set; }
    }

Your query would be like this:

 using (var db = new EFDemoContext())
 {
     var query = db.Tests.Where(t => t.Id > 0).ToList().Select(t => new TestDto() {
                    Id = t.Id,
                    IntField1 = Convert.ToInt32(t.Field1),
                    BoolField = t.Field2 == "T"?true:false,
                    IntField2 = int.Parse(t.Field3)
        });

  }

Best regards,

Zhanglong Wu

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].