CODEWITHSUNDEEP
c#.netsql-serverlinqentity-framework
Stefan Bossbaly
Stefan Bossbaly

Reputation: 6804

Entity Framework Navigation Property One-to-Many becomes One-to-One

In my database I have a One-to-Many relationship (Band has many Albums). However this relationship becomes One-to-One when a foreign key in Album is restricted.

public class Band
{
    [Key]
    public int BandID { get; set; }

    //Other fun attributes

    public virtual ICollection<Album> Albums { get; set; }
}

public class Album
{
    [Key]
    public int AlbumID { get; set; }

    public int BandID { get; set; }

    //Other fun attributes

    //Some other foreign key
    public int SomeOtherKey { get; set; }
}

SQL that should be generated

SELECT * FROM Band
LEFT JOIN Album ON (Band.BandID = Album.AlbumID AND Album.SomeOtherKey = 12)

My question is should I have another navigational property public virtual Album Album in Band, or since this is not always true that would be a bad idea? Should I use LINQ? What is the simplest way to accomplish this using the Entity Framework and models?

Upvotes: 1

Views: 970

Answers (2)

Stefan Bossbaly
Stefan Bossbaly

Reputation: 6804

The answer is not to use the navigational properties but to user a Linq query with a new model to hold the results like so ...

var query =
    from band in Context.Band
    join album in Context.Albums on album.BandID equals band.BandID into j1
    from j2 in j1.Where(x => x.SomeOtherKey == value).DefaultIfEmpty()
    select new BandWithAlbumModel
    {
        Band = band,
        Album = j2
    };


public class BandWithAlbumModel
{
     public Band Band { get; set; }

     public Album Album { get; set; }
}

Upvotes: 1

Eric J.
Eric J.

Reputation: 150228

If you add

public virtual Album Album;

that would conflict with 

public virtual ICollection<Album> Albums { get; set; }

The first implies a 1:1 relationship, while the later implies a 1:N relationship.

Since presumably a Band can have multiple (or no) Albums (domain knowledge here :-), the ICollection is what you most likely want.

Depending on your needs, you may want to add

public virtual Band Band { get; set;}

to Album.

I do not understand what you mean by

However this relationship becomes One-to-One when a foreign key in Album is restricted.

Could you clarify that remark?

Upvotes: 0

Related Questions