Linq to Entities and POCO foreign key relations mapping (1 to 0..1) problem

Question

For my ASP.NET MVC 2 application I use Entity Framework 1.0 as my data access layer (repository). But I decided I want to return POCO. For the first time I have encountered a problem when I wanted to get a list of Brands with their optional logos. Here's what I did:

public IQueryable<Model.Products.Brand> GetAll()
    {
        IQueryable<Model.Products.Brand> brands = from b in EntitiesCtx.Brands.Include("Logo")
                                                  select new Model.Products.Brand()
                                                         {
                                                             BrandId = b.BrandId,
                                                             Name = b.Name,
                                                             Description = b.Description,
                                                             IsActive = b.IsActive,
                                                             Logo = /*b.Logo != null ? */new Model.Cms.Image()
                                                                    {
                                                                        ImageId = b.Logo.ImageId,
                                                                        Alt = b.Logo.Alt,
                                                                        Url = b.Logo.Url
                                                                    }/* : null*/
                                                         };
        return brands;
    }

You can see in the comments what I would like to achieve. It worked fine whenever a Brand had a Logo otherwise it through an exception that you can assign null to the non-nullable type int (for Id). My workaround was to use nullable in the POCO class but that's not natural - then I have to check not only if Logo is null in my Service layer or Controllers and Views but mostly for Logo.ImageId.HasValue. It's not justified to have a non null Logo property if the id is null.

Can anyone think of a better solution?

Answer 1

Hi,

just a option

public IQueryable<Model.Products.Brand> GetAll()
{
    IQueryable<Model.Products.Brand> brands = from b in EntitiesCtx.Brands
                                              let logo =EntitiesCtx.Logos.First(c=>c.LogoId==b.LogoId);
                                              select new Model.Products.Brand()
                                                     {
                                                         BrandId = b.BrandId,
                                                         Name = b.Name,
                                                         Description = b.Description,
                                                         IsActive = b.IsActive,
                                                         Logo = /*b.Logo != null ? */new Model.Cms.Image()
                                                                {
                                                                    ImageId = logo.ImageId,
                                                                    Alt = logo.Alt,
                                                                    Url = logo.Url
                                                                }/* : null*/
                                                     };
    return brands;
}

I guess it's better than square select brandsXlogos, instead of this I propose to use JOIN Let me know please if you'll find this helpful

Answer 2

One option would be to not allow null logos in the database in the first place and have a special logo id=0 in the database that is the no-logo logo. Put that on any brand for which you don't have a logo. i.e. Null Value pattern.

If you don't like having a hard coded id, create a bit value "IsNullLogo" on the logo and use that to find the logo id initially (since it might have a different ID in production to development ...).

Answer 3

I have another workaround for this. Since in some cases the Image (the class for Logo property) can't be null and in some it can I decided to add some inheritance to my model of the Image. Here's what I've done:

public class OptionalImage
{
    public long? ImageId
    {
        get;
        set;
    }

    [DisplayName("Obraz")]
    [StringLength(200, ErrorMessage = "Url jest za długi")]
    [RegularExpression(@".*\.(jpg|gif|png|jpeg|tif|tiff|JPG|GIF|PNG|JPEG|TIF|TIFF)$", ErrorMessage = "Rozszerzenie pliku jest nieprawidłowe. Dopuszczone to: .jpg, .gif, .png, .jpeg, .tif, .tiff")]
    public string Url
    {
        get;
        set;
    }

    [StringLength(200, ErrorMessage = "Tekst alternatywny jest za długi")]
    [DisplayName("Tekst alternatywny")]
    public string Alt
    {
        get;
        set;
    }
}

public class Image : OptionalImage
{
    public new long ImageId
    {
        get;
        set;
    }
}

Then my repository returns a nullable Id only for the objects that have 0..1 relation with Image - like Brand. But a Product which has a required DefaultImage property will use Image without nullable Id which is corresponding to the database and business logic design.

The GetAll method in the repository now looks like this (thanks @omoto for the tip):

public IQueryable<Model.Products.Brand> GetAll()
    {
        IQueryable<Model.Products.Brand> brands = from b in EntitiesCtx.Brands
                                                  let logo = EntitiesCtx.Images.FirstOrDefault(c => c.ImageId == b.Logo.ImageId)
                                                  select new Model.Products.Brand()
                                                         {
                                                             BrandId = b.BrandId,
                                                             Name = b.Name,
                                                             Description = b.Description,
                                                             IsActive = b.IsActive,
                                                             Logo = new Model.Cms.OptionalImage()
                                                                    {
                                                                        ImageId = logo.ImageId,
                                                                        Alt = logo.Alt,
                                                                        Url = logo.Url
                                                                    }
                                                         };
        return brands;
    }

For products instead of new Model.Cms.OptionalImage() I will use new Model.Cms.Image() and since the value of ImageId database filed in that case can't be null it will work fine and everything will be natural in the controllers and views. I think this kind of workaround suits my needs very well. Still if anyone has a better solution feel free to reply.