Join two lists returns IEnumerable in IEnumerable C# Entity framework

Question

Im trying to join two lists, but the result gives me an IEnumerable in a IEnumerable when Im expecting only simple List. I can cast both IEnumerables to lists, but Im confused why this happens in the first place. Why is this and what do I need to do in order to get only one list from a join?

namespace arena.DAL
{

public class EquipmentData
{
    private Arena db = new Arena();

    public ArmorModel getArmor()
    {
        var user = UserData.getCurrentUser();

        var equipmentArmorList = db.EquipmentModels.Where(e => e.AccountId == user.Id && e.EquipmentType == (int)Constants.EquipmentConstants.EquipmentType.Armor).ToList();

        var armorList = db.ArmorModels.ToList();

        var result = from f in equipmentArmorList
                     join s in armorList
                     on f.EquipmentId equals s.Id into g
                     select g.ToList();
        return result;
    }

}
}


public class EquipmentModel
{
    public EquipmentModel()
    {

    }

    public int Id { get; set; }
    public string AccountId { get; set; }
    public int EquipmentId { get; set; }
    public int EquipmentType { get; set; }
    public bool Equipped { get; set; }
}

public class ArmorModel
{
    public ArmorModel()
    {

    }
    public int Id { get; set; }
    public int Level { get; set; }
    public string Name { get; set; } 
    public string Biographi { get; set; }
    public double Health { get; set; }
    public double Stamina { get; set; }
    public double Strength { get; set; }
    public double Initiative { get; set; }
    public double Avoidance { get; set; }
    public double Armor { get; set; }
    public double Durability { get; set; }
    public int MinDamage { get; set; }
    public int MaxDamage { get; set; }
    public int ExperienceGain { get; set; }
    public int CurrencyGain { get; set; }
    public int Cost { get; set; }
    public int Slot { get; set; }
}

Answer 1

The main problem is that it is unclear what you are asking for in GetArmor.

The return value says it should return exactly one ArmorModel. Yet your query results in a List which you try to return. Of course this won't compile.

Furthermore, even after your edit, the relationship between EquipmentModel and ArmorModel is unclear.

In your query, variable f has type EquipmentModel and s has type ArmorModel. You perform a join on the EquipmentModel.EquipmentId == ArmorModel.Id.

It seems that each ArmorModel has zero or more EquipmentModels. Or in database terms: there is a one-to-many relation between ArmorModel and EquipmentModel, where EquipmentModel has a foreign key to the primary key of the ArmorModel it belongs to in property EquipmentId.

Apart from the fairly confusing choice in property naming, this one-to-many relation is not modeled correctly in your classes. If you want Entity Framework to represent your one-to-many relation ship, the class definitions ought to be like:

See this article about proper one-to-many Entity Framework relations

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

    // an ArmorModel has many EquipmentModels
    public virtual ICollection>EquipmentModel> EquipmentModels {get; set;}
    ...
}

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

    // an EquipmentModel belongs to an ArmorModel via foreign key
    public int EquipmentId {get; set;}
    public virtual ArmorModel ArmorModel {get; set;}
    ...
}

public class MyDbContext : DbContext
{
    public DbSet<EquipmentModel> EquipmentModels {get; set;}
    public DbSet<ArmorModel> ArmorModels {get; set;}
}

Because of the mismatch between table names / foreign keys etc some Attributes or fluent API is needed. Consider changing the name of property EquipmentId to the proper defaults ArmoreModelId. Entity Framework will understand the one-to-many relationship automatically:

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

    // an EquipmentModel belongs to an ArmorModel via proper default foreign key
    public int ArmorModelId {get; set;}
    public virtual ArmorModel ArmorModel {get; set;}
    ...
}

Now back to your question. Since there is such a big mismatch between your query and the type you should return, it is impossible to determine what you want.

If seems you want one special ArmorModel from the collection of all ArmorModels. It seems you want the one-and-only ArmorModel that has one or more EquipmentModels that match certain properties.

After the proper one-to-many definition your query would be much simpler:

var matchingArmoreModels = db.ArmorModels
    // I don't want all ArmorModels,
    // I only want those ArmorModels that have at least one EquipmentModel
    // that match certain conditions with AccountId and EquipmentType
    .Where(armorModel => armorModel.EquipmentModels
        .Where(equipmentModel => equipmentModel.AccountId == user.Id
            && equipmentModel.EquipmentType == ...)
        // it is enough if it has at least one matching EquipmentModel:
        .Any());

Now you have all ArmorModels that have at least one EquipmentModel with the correct AccountId and EquipmentType.

If and only if you are certain that there should be one element, you can return Single:

ArmorModel myOneAndOnlyArmorModel = matchingArmorModels.Single();
return myOneAndOnlyArmorModel;

If you think there might be no matching ArmorModel, use SingleOrDefault. If you think there might be several matching ArmorModels return either your complete sequence, or return the first matching one: FirstOrDefault.

Finally: it could be that an EquipmentModel does not belong to exactly one ArmorModel, but that it can belong to several ArmorModels. In that case the one-to-many relation is a many-to-many relation, which is described here.

Configure one-to-many in Fluent API

If you use the entity framework code first conventions, you don't have to explicitly define your one-to-many relations. Entity Framework will detect the automatically.

However if you want to use non-standard names for your foreign keys or for your navigation properties, like you do with the foreign key in EquipmentModel you'll have to help entity-framework.

The easiest to describe this in your override of DbContext.OnModelCreating

Every EquipmentModel belongs to exactly one ArmorModel, using foreign key EquipmentId. Every ArmorModel has many EquipmentModels in property EquipmentModels

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<EquipmentModel>()
       .HasRequired(equipmentModel => equipmentModel.ArmorModel)
       .WithMany(armorModel => armorModel.EquipmentModels)
       .HasForeignKey(equipmentModel => equipmentModel.EquipmentId);
}

Alternatively you could configure the ArmorModel similarly: An ArmorModel has many EquipmentModelss. Every EquipmentModel belongs to exactly one ArmorModel, using foreign key EquipmentId.

modelBuilder.Entity<ArmorModel>()
    .HasMany(armorModel => armorModel.EquipmentModels)
    .WithRequired(equipmentModel => equipmentModel.ArmorModel)
    .HasForeignKey(equipmentModel => EquipmentId);

Be sure not to specify both. After all: there is only one one-to-many relation