none
Convert string columns to int properties RRS feed

  • 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?

    Monday, November 20, 2017 1:48 AM

All replies

  • 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 MSDNFSF@microsoft.com.

    Monday, November 20, 2017 6:02 AM
    Moderator