none
Multiple Providers RRS feed

  • Question

  • Hi,

    I am trying to implement a Data Model using EF5, that will possibly be used from SQL Server or SQL Lite database.

    I would like to share the same model and therefore local objects across both of these, but SQL Lite stores int fields as Int64 (SQL bigint). I do not want to convert existing database columns to bigint.

    I would like to know if there is a way to relax the type checking to enable me to convert int64 objects to int32 properties on a local object?

    If this is not possible and I can manually map the ssdl fields to int64 columns, is it possible to use linq join/where statements on either of the int32 or int64 fields?

    ie instead of always having to use:

    if provider = sql 1 then select id where id = 1 else select id where id64 = 1

    I would prefer to use:

    select id or id64 where idalias = 1

    Thanks,

    Carl

    Friday, May 10, 2013 3:20 PM

Answers

  • Hi Carl,

    I think you can try to modify SSDL to map int property of the entity type to bigint in the database. But you need to make sure the value will not be overflow.

    There is another idea to add another property in the partial class. You can use this property in your application which maps to the generated property. Some code snippet like:

    http://forums.asp.net/t/1753681.aspx/1

    Best regards,


    Chester Hong
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, May 14, 2013 5:47 AM
    Moderator
  • Hi Carl_IT,

    You said that type checking reports it as invalid. Is it reported by designer or there is an exception thrown at runtime?

    If you are using a property that is not mapped, you cannot use LINQ to Entites since only properties like scalar properties or navigation properties are supported.

    However, you can use ToList() or ToArray() extension method to query the database first. And then perform LINQ to Objects. For example:

    var items = context.Entity1.ToList();
    var item = items.Where(en => en.AddtionalProperty == 123).FirstOrDefault();
    
    Best regards,

     


    Chester Hong
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, May 15, 2013 3:05 AM
    Moderator

All replies

  • Hi Carl,

    I think you can try to modify SSDL to map int property of the entity type to bigint in the database. But you need to make sure the value will not be overflow.

    There is another idea to add another property in the partial class. You can use this property in your application which maps to the generated property. Some code snippet like:

    http://forums.asp.net/t/1753681.aspx/1

    Best regards,


    Chester Hong
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, May 14, 2013 5:47 AM
    Moderator
  • Hi Chester,

    Thanks for the response. I did indeed manage to re-map the property, although it wasn't from a bigint to an int property, as the type checking reports this an invalid. I instead added an additional Int64 property, to be a private property, only used for Entity mappings.

    This gave me the second problem, where because only my Int64 column was mapped, I cannot perform LINQ queries using the Int32 property in the where clause or join. Can anybody suggest a way around using a column that isn't mapped to still generate a where clause in the Object Query?

    Tuesday, May 14, 2013 7:03 AM
  • Hi Carl_IT,

    You said that type checking reports it as invalid. Is it reported by designer or there is an exception thrown at runtime?

    If you are using a property that is not mapped, you cannot use LINQ to Entites since only properties like scalar properties or navigation properties are supported.

    However, you can use ToList() or ToArray() extension method to query the database first. And then perform LINQ to Objects. For example:

    var items = context.Entity1.ToList();
    var item = items.Where(en => en.AddtionalProperty == 123).FirstOrDefault();
    
    Best regards,

     


    Chester Hong
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, May 15, 2013 3:05 AM
    Moderator