none
Linq to SQL - foreign table datetime null RRS feed

  • Question

  • Hi,

    I have an odd issue with some LinqToSql:

    I am pulling data from a table that has a foreign related table. The foreign table will not always have a record, so I need to be able to deal with that.

    In the code below, I am retrieving records from the database and then creating a new FundDetailDTO per record. There may or may not be a related record for the AlternateFund. If there is, then its no issue, if not, then I need to deal with the lack of data. For the 'InitialMinimum' and 'RedemptionMinimum' the '==null ?' works just fine, but the date does not.

    The Error I get is System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

    I cannot understand why cos a.) cpi.AlternateFundCloseToBuy us null so it should just create a new DateTime Object. I have tried various way of doing this but just get the same error. I have checked that its not the 'new datetime' causing the error.

    Oddly enough, If I try 'FundName  = cpi.AlternateFund.closeToBuyDate == null ? "1st" : "2nd" ' then it works perfectly, proving that the condition clause is correct and behaves as expected. BUT, as soon as I try to assign to the CloseToBuyDate field of the DTO I get the above error?????

    I have also tried having the DateTime in the DTO as a DateTime? but I still get the same error.

    Thanks in advance for help.

    'cpi' is the first level table(FundItems) and 'AlternateFund' is the related table

    FundDetailDTOCollection q = (from cpi in db.FundItems
    where cpi.ProductTypeId == productCode
    
    select new FundDetailDTO
    {
     ProductCode = cpi.ProductTypeId
    , CloseToBuyDate = cpi.AlternateFund.closedToBuyDate == null ? new DateTime() : cpi.AlternateFund.closedToBuyDate 
    
    , InitialMinimum = cpi.AlternateFund.initialMinimum == null ? 0 : (decimal)cpi.AlternateFund.initialMinimum
    
    , RedemptionMinimum = cpi.AlternateFund.redemptionMin== null ? 0 : (decimal)cpi.AlternateFund.redemptionMin
    
    }).ToFundDetailCollection();
    
    
    

    (NOTE : ToFundDetailCollection is an extention method)

     

    Tuesday, November 23, 2010 7:14 PM

Answers

  • Hi,

    Sometimes in order to write less code it is used to have SQL server set fields like date, time and ID on insert by setting the default value for fields to GETDATE() or NEWID().

    In such cases StoreGeneratedPattern property of those fields in entity classes should be set to Identity (StoreGeneratedPattern = "Identity"). This property must by set by hand at SSDL schema as shown here:

    http://geeksharp.com/2010/05/27/ef4-bug-in-storegeneratedpattern-ssdl/

    This way you do not need to set values in code and never see that exception.

    Best regards,

    JAReyes.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solucion de esta pregunta te ha sido útil.
    • Marked as answer by liurong luo Monday, December 6, 2010 10:42 AM
    Tuesday, November 23, 2010 8:11 PM

All replies

  • Hi,

    Sometimes in order to write less code it is used to have SQL server set fields like date, time and ID on insert by setting the default value for fields to GETDATE() or NEWID().

    In such cases StoreGeneratedPattern property of those fields in entity classes should be set to Identity (StoreGeneratedPattern = "Identity"). This property must by set by hand at SSDL schema as shown here:

    http://geeksharp.com/2010/05/27/ef4-bug-in-storegeneratedpattern-ssdl/

    This way you do not need to set values in code and never see that exception.

    Best regards,

    JAReyes.


    Please remember to Vote & "Mark As Answer" if this post is helpful to you.
    Por favor, recuerda Votar y "Marcar como respuesta" si la solucion de esta pregunta te ha sido útil.
    • Marked as answer by liurong luo Monday, December 6, 2010 10:42 AM
    Tuesday, November 23, 2010 8:11 PM
  • Hello,

    Thanks for your code snippet and other detail information! It’s really helpful!

    The new DateTime() in the linq query will be sent to sql server as a parameter to get the result. However, by default, a new datetime is of value 1/1/0001 12:00:00 AM. It is out of the scope of the datetime type in sql server, hence, the exception occurs.

    JA Reyes has suggest a good way to generate datetime in database automatically, it enables your columns in database is not null. One workaround is to create a more concrete time, for example, using DateTime.Now instead of new DateTime() or manually specify the year, month, day with new DateTime(2010, 11, 24).

     


    Best Regards,
    Roahn Luo
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com
    Wednesday, November 24, 2010 2:58 AM
  • Hello,

    I'm writing to follow up this thread. Please let us know if you have more concern on this issue, we'll be very glad to provide help.

    Thanks a lot!

    -Best Regards,
    Roahn Luo
    MSDN Subscriber Support in Forum
    If you have any feedback on our support, please contact msdnmg@microsoft.com
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Welcome to Microsoft All-In-One Code Framework to download or request code samples from Microsoft Community Team!
    Monday, December 6, 2010 10:42 AM