Entity Framework 5.0 code first one to one and one to many relationship

Question

I have two entities- Product and Picture. The idea is that a product can have only one picture or none and a picture can be mapped to many products.

public class Product
{
    public int Id {get; set;}

    public Picture Picture { get; set; }

    public bool Enabled { get; set; }

    public string Text { get; set; }
}

The Picture entity is a mapping to another Pictures entity that contains all of the pictures (this Picture entity contains a mapping only for the pictures for the products), so basically it contains only two columns with Ids.

public class Picture
{
    public int Id {get; set;}

    public int PictureId { get; set; }
}

This is my model binder for the Product entity

modelBuilder.Entity<Product>().HasOptional<Picture>(pr => pr.Picture);

When I create a Product entity with picture, or add a picture to an existing entity the Picture_Id column that is created in the Product table is correctly populated with the Id from the Picture table. The problem is when I try to retrieve the Product. The Picture entity inside the Product entity is null. I suspect that the mapping is incorrect. Can you tell me how to populate the Picture entity inside the Product entity when I retrieve it. Here is the retrieval code:

 public Product GetProductById(int productId)
    {
        var query = from pr in _productRepository.Table
                    where pr.Id == productId
                    select pr;

        return query.FirstOrDefault();
    }

---

EDIT: I am using custom IRepository service that I include with dependency injection

IRepository<Product>

The IRepository interface contains the following methods:

    void Insert(T entity);
    void Update(T entity);
    void Delete(T entity);
    IQueryable<T> Table { get; }

This is the implementation of Table:

 public virtual IQueryable<T> Table
    {
        get
        {
            return this.Entities;
        }
    }

    private IDbSet<T> Entities
    {
        get
        {
            if (_entities == null)
                _entities = _context.Set<T>();
            return _entities;
        }
    }

I can not add .Include("Picture") to the Table, because it is IQueryable. I also enabled the Lazy Loading, but there were no result.

Answer 1

I fixed the problem. Not sure if this is the correct way, but it works.

I did the following changes:

    public class Product
{
    public int? PictureId { get; set; }

    public int Id {get; set;}

    public Picture Picture { get; set; }

    public bool Enabled { get; set; }

    public string Text { get; set; }
}

modelBuilder.Entity<Product>().HasOptional<Picture>(pr => pr.Picture).WithMany().HasForeignKey(pr => pr .PictureId);

public Product GetProductById(int productId)
    {
        var query = from pr in _productRepository.Table
                    where pr.Id == productId
                    select pr;

        var product = query.FirstOrDefault();
        AddProductPicture(product);

        return product;
    }

private void AddProductPicture(Product product)
{
    var query = from pic in _pictureRepository.Table
                        where pic.Id == product.PictureId
                        select pic;

            product.Picture = query.FirstOrDefault();
}

Answer 2

you should use include for loading related entities :

public Product GetProductById(int productId)
{
    using (var db = new ProductContext())
    {
       var query = from pr in db.Products.Include("Picture")
                   where pr.Id == productId
                   select pr;

        return query.FirstOrDefault();
    }
}

Answer 3

var products = db.Pictures.Include("Product");

public Product GetProductById(int productId)
    {
        var query = from pr in products
                    where pr.Id == productId
                    select pr;

        return query.FirstOrDefault();
    }