C# LINQ Query Hanging

Question

I'm working to create a c# application, and in a portion of the application; I'm looking to bring in a .csv to a data table; and then basically loop through each row and query a database to see if the data exists.

I'm testing a LINQ query; but I can't seem to get it to run and display anything. I have the following code setup to run below:

I have the database added and the connection tests succesfully; I have the classes setup. I've been following some courses on pluralsight to test; and I'm not sure what exactly I am doing wrong or missing.

Also as a note; the table name is actually ERP.PartTran, and not PartTran, but I wasn't succesful in setting that up for the db context; could that be why?

EDIT: Code added; images removed

public class EpiDB : DbContext
        {
            public DbSet<Tran> PartTran { get; set; }
        }

        public class Tran
        {
            public int TranNum { get; set; }
            public string TranReference { get; set; }
            public string PartNum { get; set; }

        }

        private static void QueryPartTran()
        {
            var db = new EpiDB();
            int tranref = 4650374; //lookup number
            var query = from Tran in db.PartTran
                        where Tran.TranNum == tranref
                        orderby Tran.TranNum
                        select Tran;

            foreach (var Tran in query)
            {
                Debug.Print(Tran.PartNum);
            }
        }

Answer 1

If you have an existing database schema, the first thing to avoid soft exceptions is to disable schema creation/migration in EF. By default when EF connects to a database and goes to resolve the schema, if it comes across a table that it cannot resolve, it will create it. The clue I see that might be happening in this case is when you say the table is called [ERP].PartTran. I suspect you may find that your database has a new empty table called [dbo].Tran. (assuming SQL Server)

To disable schema creation: In your Db Context constructor

public EpiDB()
{
   Database.SetInitializer<EpiDB>(null);
}

This may go a long ways to identifying any bad schema assumptions that EF is making by convention. Jim's answer would be along the lines of where I would believe your problem will lie.

Entities should map relatively closely, if not identically to your table. Renaming an entity or properties to differ from the table to clarify it in code is fine, but you need to be sure that when you do this, you give EF enough information about your schema so that it can resolve the table correctly. If your table is named "PartTran" and your DbSet instance is named "PartTran", why would you want to name the entity "Tran" rather than "PartTran"?

If your application schema is "ERP" then you can avoid needing to specify the schema name on each entity by adding the following to your DbContext.OnModelCreating():

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    modelBuilder.HasDefaultSchema("ERP");

    // ...
}

Otherwise, if you are using multiple schemas then you will need to explicitly map the schema to use with a [Table] attribute or ToTable("{tableName}", "{SchemaName}") in EntityTypeConfig / modelBuilder config.

Next, ensure that your entity fields match the appropriate fields in the table. You don't need to map every field if you don't need them, but at a minimum you do need to map the Primary Key. On a guess from the PartTran entity, I'm guessing you're either missing something like a PartTranId column, or the PK is a composite key using the PartNum and TranNum columns. If you have a PartTranId or similar PK, add it to the entity along with a [Key] attribute. If the PK is a composite:

    public class PartTran
    {
        [Key, Column(Order = 1)]
        public int TranNum { get; set; }
        public string TranReference { get; set; }
        [Key, Column(Order = 2)]
        public string PartNum { get; set; }
    }

This should give you a few ideas to check out against your code base... To go further it would help to amend your question to include the related tables and any entities you have tried creating so far. Something like "PartTran" looks like a joining table for a many-to-many relationship between a "Part" table and a "Tran"(saction?) table. If that is the case there are a number of options how you can efficiently wire this up in EF to get the data out the way you want.

Answer 2

Try this:

    public class EpiDB : DbContext
    {
        public DbSet<Tran> PartTran { get; set; }
    }

    [Table("PartTran", Schema = "ERP")]
    public class Tran
    {
        public int TranNum { get; set; }
        public string TranReference { get; set; }
        public string PartNum { get; set; }
    }

And maybe even:

    public class EpiDB : DbContext
    {
        public DbSet<Tran> PartTran { get; set; }
    }

    [Table("PartTran", Schema = "ERP")]
    public class Tran
    {
        [Key] // Is this your primary key field?
        public int TranNum { get; set; }
        public string TranReference { get; set; }
        public string PartNum { get; set; }
    }