Using two tables from the database with the Entity Framework

Question

I'm starting with Entity Framework, and I'm wondering if I can access each of the tables from the main name = "DBEntities" approach?

This is my code, and it is not clear to me why I am not getting the data in table2 if I am already using a basic approach to the database.

App.config

<connectionStrings>
    <add name="DBEntities" 
         connectionString="metadata=res://*/DBModel.csdl|res://*/DBModel.ssdl|res://*/DBModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=DESKTOP-GN4506J;initial catalog=Test;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" 
         providerName="System.Data.EntityClient" />
</connectionStrings>

Form1.cs

public partial class FormMenu : Form
{
    Table1 firstTable = new Table1 ();
    Table2 secondTable = new Table2();
    Table3 thirdTable = new Table3();

    using (DBEntities db = new DBEntities())
    {
        try
        {
            firstTable = db.Table1.Where(x => x.BATCH_ID == cardUID).FirstOrDefault();

            if (firstTable == null)
            {
                return;
            }
            else
            {
                thirdTable = db.Table3.Where(x => x.LPE_ID == firstTable.MA_ID).FirstOrDefault();

                secondTable = db.Table2.Where(x => x.ZPZ_Datum.Value.Date == DateTime.Now.Date && x.ZPZ_LPE_ID == firstTable.MA_ID).LastOrDefault();

                // here the application shoots me because in secondTable I don't get data based on db.Table2, where am I making a mistake?
                if (thirdTable == null)
                {
                    db.Entry(thirdTable ).State = System.Data.Entity.EntityState.Modified;
                    db.SaveChanges();
                }
            }
        }
        catch (Exception es)
        {
            MessageBox.Show(es.Message);
        }
    };
}

error message :

'LINQ to Entities' does not recognize the 'ApplicationTest.Table2 LastOrDefault [Table2] (System.Linq.IQueryable`1 [ApplicationTest.Table2])' method, and this method cannot be translated into a memory expression.

Can I not access both tables this way? Why then do I get in the first db.Table1 data? Can anyone explain this to me and give me a solution.

Can you please elaborate more on this line? *I don't get data based on db.Table2* — Salah Akbari, Apr 02 '20 at 13:10
The issues with the posted code leads me to advise you to look up an Entity Framework tutorial. In EF, you simply don't work with tables directly (that's the reponsibility of EF), you work with collections of entities. But a StackOverflow answer shouldn't be a basic tutorial on what is a rather big library and this is IMHO out of scope. — Flater, Apr 02 '20 at 13:11

Salah Akbari · Accepted Answer · 2020-04-02T15:45:03.210

1

Well it seems LastOrDefault couldn't be translated to T-SQL, Linq to Entities couldn't recognize it. You can modify the code as below:

secondTable = db.Table2
 .Where(x => x.ZPZ_Datum.Value.Date == DateTime.Now.Date 
           && x.ZPZ_LPE_ID == firstTable.MA_ID)
 .OrderByDescending(c => c.SomeColumn)
 .FirstOrDefault(); //Or Take(1)

As an another alternative you can use .ToList() or AsEnumerable methods after your Where statement as well just like this answer https://stackoverflow.com/a/60896583/2946329, but you should be aware, by using them after data is loaded, any further operation is performed using Linq to Objects, on the data already in memory. So if you would care about the performance I am not sure using this approach be a good choice in your case and I can't guarantee that!

edited Apr 02 '20 at 15:45

answered Apr 02 '20 at 13:14

Salah Akbari

39,330
10
79
109

now i get this error: "Error CS0266 The type "System.Linq.IQueryable " cannot be implicit be converted into "ApplicationTest.Table2". It is already an explicit conversion exists (a conversion may be missing)." – Mara Apr 02 '20 at 13:25
@Mara Use `FirstOrDefault()` instead of `Take`. – Salah Akbari Apr 02 '20 at 13:30
Thank you for your help. I get now this error: "The specified type element 'Date' is not supported in 'LINQ to Entities'. Only initializers, entity elements, and entity navigation properties are supported." – Mara Apr 02 '20 at 13:33
1

@Mara Since you have used a lot of methods which can't be converted I think it would be better to use ToList like this: `db.Table2.ToList().Where...` – Salah Akbari Apr 02 '20 at 13:37
thank you very much, I didn't know I needed toList () when the table was called – Mara Apr 02 '20 at 13:39

Using two tables from the database with the Entity Framework

1 Answers1