CODEWITHSUNDEEP
asp.netsql-serverentity-framework
Jonathan Kuhl
Jonathan Kuhl

Reputation: 709

Related Table in Entity Framework coming up as null

I can't work out why this is happening. I have two tables (right now, when finished I'll have well more than two) for a medical company. The primary table is called NPPR_Provider and it holds a foreign key to NPPR_Header. It's a One To One relationship, only one Header per Provider.

Right now I've got a simple console application to read the database using Entity Framework, I do intend later, once I know I've got the models correctly built, use them in a ASP.NET MVC application.

I have no problem connecting to NPPR_Provider and getting data off of it. That's working fine. Connection string and model etc are all working. But NPPR_Header keeps coming up as null and I can't figure out why.

Both tables exist in the database (SQL Server) as:

[AD].[NPPR_Provider]
[AD].[NPPR_Header]

NPPR_Provider model:

namespace Company.Name.Application.Models
{
    [Table("NPPR_Provider", Schema ="AD")]
    public class NPPR_Provider
    {
        [Key]
        public int NPPRProvId { get; set; }

        public int NPPRFileId { get; set; }
        public virtual NPPR_Header NPPR_Header { get; set; }

        public string First { get; set; }
        public string Middle { get; set; }
        //... and more column names I won't bore you with
    }

    public class NPPR_ProviderContext : DbContext
    {
        private string _state;
        public NPPR_ProviderContext(string state)
        {
            _state = state.ToUpper();
        }

        public DbSet<NPPR_Provider> NPPR_Provider { get; set; }
        public DbSet<NPPR_Header> NPPR_Header { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(
                $"server=DEVSERVER;database=DataBaseName_{_state};trusted_connection=true;");
        }

    }
}

NPPR_Header

namespace Company.Name.Application.Models
{
    [Table("NPPR_Header", Schema = "AD")]
    public class NPPR_Header
    {
        [Key]
        public int NPPRFileId { get; set; }
        public string FileName { get; set; }

    }
}

And the code that's using it, in class SQLUtilities:

public static string test()
{
    using (var db = new NPPR_ProviderContext("ks"))
    {
        if (db.Database.CanConnect())
        {
            var npprFile = db.NPPR_Provider
                .Where(provider => provider.First == "Bob");
            int count = 0;
            foreach(var result in npprFile)
            {
                Console.WriteLine(result.NPPR_Header.FileName);
                count++;
            }
            return $"Complete, {count} records found";
        }
        else
        {
            return "Failed to connect";
        }
    }
}

Above I'm trying to simulate part of what my app will need to do, query the database by provider name and get a filename for a specific JSON file I need to provide to the user.

Console application (I plan to trash this once I know my models work):

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            Console.WriteLine(SqlUtilities.test());
            Console.ReadLine();
        }
    }
}

Now there is data, because the logical equivalent in SQL to the test() method is:

SELECT h.FileName FROM AD.NPPR_Provider p 
    INNER JOIN AD.NPPR_Header h 
    ON p.NPPRFileId = h.NPPRFileId 
    WHERE p.First = 'Bob';

And it gives me 60+ records with all the filenames I'm looking for. The foreign keys and primary keys are in place in the tables The C# code gives me the same number of records, but they're all null. The code crashes (if I try to access the FileName property like above) with an error explicitly saying 

Company.Name.Application.Models.NPPR_Provider.NPPR_Header.**get** returned null.

I can't figure out why NPPR_Header is null. The connection string works, I can get information from NPPR_Provider. NPPR_Provider is not null. The foreign key and NPPR_Header properties are on NPPR_Provider. They also exist in the database.

It might be something incredibly stupid, I'm new to Entity Framework and I've been going through multiple articles and Stack Overflow questions but I'm missing something.

Upvotes: 0

Views: 860

Answers (1)

FLICKER
FLICKER

Reputation: 6683

I think you are missing the Include method.

if you want to load the child entities you need to use it like below:

context.Customers
        .Include(c => c.Invoices)    
        .ToList();

In you case you need to use 

var npprFile = db.NPPR_Provider
               .Include(provider.NPPR_Header)
               .Where(provider => provider.First == "Bob");

Upvotes: 1

Related Questions