none
EF6, LINQ, Computed Field - Only initializers,... are supported RRS feed

  • Question

  • Hello

    I have a table which is represented by this POCO class

    public partial class Chemistry
        {
            public double? C { get; set; }
            public double? C_Std { get; set; }
    
            public double? Si { get; set; }
            public double? Si_Std { get; set; }
            public double? Fe { get; set; }
            public double? Fe_Std { get; set; }
    
            // ... +20 or more properties
        }
    
    
        public partial class Chemistry
        {
            public double? C_Min { get { return C - C_Std; } }     // I simplify this expression and left out check for NULL values
            public double? C_Max { get { return C + C_Std; } }     // I simplify this expression and left out check for NULL values
    
            // ... +20 or more properties
        }


    I want to query database and check if user values are between min and max (which are not mapped to database).

    For example:
    I am searching for those records that have chemical element C between min and max (which is calculated using C_Std = standard deviation)

    User can also combine conditions. For example:
    I am looking for C = 0.9  and Si = 1.3
    We are not looking for exact values, but we need to check if:
       value 0.9 is between C_Min and C_Max    and
       value 1.3 is between Si_Min and Si_Max

    Something like this:

    var results = db.Chemistry.Where ( multiple conditions ).ToList();

    Unfortunately all my attempts end up with runtime error message:

    // The specified type member is not supported in LINQ to Entities occurs when a new property is added in the partial class

    I read something about calling ToList() before where. To first materialize entities and then filter it out, but there might be more than 100.000 records in the table.

    Can someone help me out, please?

    Regards

    Friday, December 13, 2019 9:00 AM

All replies

  • I also found this 

    https://stackoverflow.com/questions/34562382/multiple-where-clauses-on-an-entity-framework-queryable/34562432

    I apply suggested solution to my structure and end up with this  (but still not working)

        var queryable = db.Chemistry;
        // Filter
        var userC = FormData.C;
        var userSi = FormData.Si;
        queryable = queryable.Where(x => x.C_Min <= userC && userC <= x.C_Max);
        queryable = queryable.Where(x => x.Si_Min <= userSi && userSi <= x.Si_Max);
    
    Gives me error that C_Min and C_Max is not supported.

    Friday, December 13, 2019 9:09 AM
  • varresults =db.Chemistry.Where (multiple conditions ).ToList();

    The specified type member is not supported in LINQ to Entities occurs when a new property is added in the partia

    Linq-2-Entites cannot make the T-SQL to be submitted based on something that is not originally on the Model class.

    Friday, December 13, 2019 3:43 PM
  • I suggest that you use raw T-SQL in EF and call a stored procedure or use the EF backdoor using database command objects to execute the stored procedure and ADO.NET datareader. Linq is not a panacea solution. 
    Friday, December 13, 2019 3:49 PM
  • Hi [Beer],
    DA924x has explained in detail. Besides, you can try to set
     "Where(i => i.C > i.C-i.C_Std && i.C < i.C+ i.C_Std)" in Linq instead of defining the C_Min and C_Max property in the class.
    Here is a simple code example you can refer to.
    static void Main(string[] args)
    {
        using (var db = new BloggingContext())
        {
            var blog = new Chemistry {C=1.3,Si=9.1,C_Std=0.2};
            db.Blogs.Add(blog);
            db.SaveChanges();
            var value = db.Blogs.Where(i => i.C > i.C-i.C_Std && i.C < i.C+ i.C_Std);
        }
    }
    public class BloggingContext : DbContext
    {
        public DbSet<Chemistry> Blogs { get; set; }
    }
    Best Regards,
    Daniel Zhang

    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, December 16, 2019 9:20 AM
  • Hello Daniel,

    thanks for you answer.

    I thought about that, but the value C_Min and C_max is calculated.
    For example:
    if C_std == NULL ==> C_Min = C * 0.9     AND    C_Max = C*1.1   (+/- 10% of C)
                     else    ==> C_Min = C - 2*C_Std     AND    C_Max = C+2*C_Std
    And final check:
    if C_Min < 0   ==> C_Min = 0  
    if C_Max < 0   ==> C_Max = 0  

    So where clause is becoming very complex and unreadable (note that I have about 20 chemical elements)
    So I thought about partial class that would calculate Min and Max values (luckily Min/Max is calculated the same way for all chemical elements).
    This way I would simplify where clause to only check if the searching values is between min and max (at least I thought so. Hehe)

    Well right now I solved this by adding Min/Max columns to database (problem -> need to re-calculate min/max if std changes).
    I would still like solution without storing data that can/must be calculated.

    I read something about materializing linq queries, but haven't figure it out yet

    Regards

    Tuesday, February 25, 2020 8:16 AM