none
LinQ Query on Database Mapping and "Linq to SQL" Class is Different RRS feed

  • Question

  • [Table(Name = "TestTable")]
    public class OLData
    {
    
    [Column(Name = "Prod_date")]
        private DateTime _prod_Date;
        public DateTime Prod_date
        {
            get
            {
                return _prod_Date;
            }
            set
            {
                _prod_Date = value;
    
            }
        }
      }


    var dateCriteria = DateTime.Now.Date.AddDays(-7);
    
     var db = new DataContext(conn);
    
     Table<OLData> MyData = db.GetTable<OLData>();
    
     // no results, count = 0
     var Query_1 = from qry in MyData
                   where qry.Prod_date >= dateCriteria
                   select qry;


    var dateCriteria = DateTime.Now.Date.AddDays(-7); // Created with Linq-to-SQL class

    //Produced result DataClasses1DataContext dbx = new DataClasses1DataContext(); var Query_2 = from dh in dbx.OEEs where dh.Prod_date >= dateCriteria select dh;

    Both Query the same MSSQL database and Same table (column Datatype is shordatetime) But Query_1 rerturn NO result BUT Query_2 return desired result.

    What is wrong with Table Mapping Query? 

    Friday, April 19, 2019 3:13 AM

Answers

  • Hi JetSimon,

    What I mean is adding the ColumnAttribute "[Column(Name = "Prod_date")]" before the property instead of the field.

    As to the large amounts of data, here is a link maybe you can refer to:

    https://stackoverflow.com/a/48167892.

    Regards,

    Kyle


    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.

    • Marked as answer by JetSimon Tuesday, April 23, 2019 2:10 AM
    Tuesday, April 23, 2019 1:35 AM
    Moderator

All replies

  • Hi   JetSimon,

    Form your description, your question is more related to the LINQ to SQL, I will move it to the  
    LINQ to SQL forum.


    Thank you for your understanding.

    Best Regards

    Yong Lu

    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.

    Friday, April 19, 2019 6:06 AM
  • Hi JetSimon,

    Please try the following code that implement it by strongly typed DataContext.

        // Define entity classes
        [Table(Name = "CAR")]
        public class CARclass
        {
            [Column(Name = "Id")]
            public int Id { get; set; }
            [Column(Name = "Model")]
            public string Model { get; set; }
        }
    
        // Strongly typed DataContext
        public partial class NorthWindDataContext : DataContext
        {
            public Table<CARclass> CARs;
            public NorthWindDataContext(IDbConnection connection) : base(connection) { }
            public NorthWindDataContext(string connection) : base(connection) { }
        }
    
        static void Main(string[] args)
        {
            NorthWindDataContext NWDC = new NorthWindDataContext("connection string");
            var NWCustomers = from NWs in NWDC.CARs
                                where NWs.Id == 4
                                select NWs;
            foreach (var cst in NWCustomers.ToList())
            {
                Console.WriteLine("Id = {0}, Model = {1}", cst.Id, cst.Model);
            }
    
            Console.ReadKey();
        }

    Regards,

    Kyle


    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.

    Friday, April 19, 2019 9:49 AM
    Moderator
  • Kyle:

               The thing is  query for  something like ID or string is OK. BUT only the DateTime type that seems to be the issue.  

                  Have you try create a MSSQL DB with column type DateTime (any datetime ) and try Linq Quert for a range of time  or a specific time (==)?

                  By the way what is the purpose of strongly typed DataContext ?

    Regards

    Simon

    Friday, April 19, 2019 2:08 PM
  • Hi JetSimon,

    How did you compare the specific date? Try to refer to the following code, it works well.

        NorthWindDataContext NWDC = new NorthWindDataContext("connection string");
        var NWCustomers = from NWs in NWDC.CARs
                            // Compare
                            where NWs.Date == new DateTime(1876,10,5)
                                select NWs;

    >> By the way what is the purpose of strongly typed DataContext ?

    Its role is to create a corresponding DataContext for each table, which makes the code more concise.

    Regards,

    Kyle


    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, April 22, 2019 1:37 AM
    Moderator
  • Hi Kyle:

              I had tried with your suggestion,  same thing , row count is Zero.

    Exception thrown with the with the Foreach loop (No data ??)

    System.NotSupportedException: 'The member 'OLData.Prod_date' has no supported translation to SQL.'

    Any Idea why Query to Mapping don't produce any result?

    Thnaks

    Monday, April 22, 2019 5:34 AM
  • Hi JetSimon,

    Modify the code like this:

        private DateTime _prod_Date;
        [Column(Name = "Prod_date")]
        public DateTime Prod_date

    Regards,

    Kyle


    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, April 22, 2019 5:51 AM
    Moderator
  • Hi Kyle:

                  
            [Column(Name = "Prod_date")]
            private DateTime _prod_Date;
            public DateTime Prod_date
            {
                get
                {
                    return _prod_Date;
                }
                set
                {
                    _prod_Date = value;
                   
                }
            }

    This is exactly what I write. Another thing that I observed

      OEEDataContext OEDC = new OEEDataContext(str1);
    
    
       var data1 = OEDC.Strong_OTE.ToList()
                  .Where(x => x.Prod_date >= utcNow).ToList();
    
    
    
    

    Above code will work If performed Query after "copy" the data , but once the data get bigger... it will be ugly.

    var data2 = OEDC.Strong_OEE.Where(x => x.Prod_date >= utcNow).ToList();
    
    
    
    
    
    

    above code  will failed


    Exception Prod_date' has no supported translation to SQL.'

    I am using MSSQL Database (MSSQL Server Management  Express 2014)


    Monday, April 22, 2019 2:56 PM
  • Hi JetSimon,

    What I mean is adding the ColumnAttribute "[Column(Name = "Prod_date")]" before the property instead of the field.

    As to the large amounts of data, here is a link maybe you can refer to:

    https://stackoverflow.com/a/48167892.

    Regards,

    Kyle


    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.

    • Marked as answer by JetSimon Tuesday, April 23, 2019 2:10 AM
    Tuesday, April 23, 2019 1:35 AM
    Moderator
  • Hi Kyle:

                 Wow ! What is going on?  May I know what is the Different if the ColumnAttribute is after or Before the property?

    I thought the compiler will sort it out! 

    Thanks A lot.

    Simon

    Tuesday, April 23, 2019 2:13 AM
  • Hi JetSimon,

    This is an intrinsic form of ColumnAttribute. Here I also have a thread you can refer to:

    Linq data mapping: usage of Storage property on column attribute.

    Regards,

    Kyle


    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.

    Tuesday, April 23, 2019 2:43 AM
    Moderator
  • Hi Kyle:

                 Thanks a lot. It is really eye opener.

    Simon

    Tuesday, April 23, 2019 7:46 AM