CODEWITHSUNDEEP
linqentity-frameworkasp.net-mvc-4
Greg B
Greg B

Reputation: 813

Accessing foreign key from a one to many relationship using LINQ

I'm using MVC4 with Entity Framework code first.

I have the following models:

public class Member {

    public int ID { get; set; }

    [Display(Name = "First Name")]
    [Required(ErrorMessage = "Please enter a first name.")]
    public string FirstName { get; set; }

    [Display(Name = "Last Initial")]
    [Required(ErrorMessage = "Please enter the last initial of your last name.")]
    [MaxLength(1)]
    public string LastName { get; set; }

    [Display(Name = "City")]
    [Required(ErrorMessage = "Please enter your city.")]
    public string City { get; set; }

    public virtual ICollection<Favorite> Favorites { get; set; }
}

public class Favorite {
    public int ID { get; set; }
    public String Type { get; set; }
    public String Value { get; set; }
}

My code is receiving a list of search terms List<string> searchTerms from the front end. I need to search the values of every members' favorites for every search term.

I'm working the following LINQ statement:

return db.Favorites.Where(f => searchTerms.Any(s => f.Value.Contains(s))).ToList();

My issue is this will return a List<Favorite> which is great but what I really need is the ID of the member associated in the database. This unfortunately is not an option I can pick in LINQ

return db.Favorites.Where(f => searchTerms.Any(s => f.Value.Contains(s))).Select(f => f.????) .ToList();

The ??? only gives me ID, Type, Value. The properties of Favortie, however in the database table Entity framework created there's a foreign key column Member_ID. But I can't pick that in C#.

So my questions is... how do I? Do I just add a public virtual int MemberID in the Favorite class? Will entity framework automatically associate that to the foreign key?

Upvotes: 1

Views: 2974

Answers (3)

Mongkon Eiadon
Mongkon Eiadon

Reputation: 1

This works:

   return db.Members.Select(member => new
    {
        Member = member,
        Favorites = member.Favorites.Where(f => searchTerms.Any(s => f.Value.Contains(s)))
     }).Where(m => m.Favorites.Any()).ToList();

Upvotes: 0

Nelson Badilla Diaz
Nelson Badilla Diaz

Reputation: 11

You can get the a collection of Members with a filtered children collection.

Example:

    return db.Members.Select(member => new
    {
        Member = member,
        Favorites = member.Favorites.Where(f => searchTerms.Any(s => f.Value.Contains(s)))
     }).Where(m => m.Favorites.Any()).ToList();

Don't forget the IEqualityComparer, or convert to uppercase or lowercase both the search terms and the value

Upvotes: 1

Esteban Elverdin
Esteban Elverdin

Reputation: 3582

Based on Code First conventions, for adding the foreign key just:

public int MemberID { get; set; }

If you need the navigation property then 

public virtual Member Member { get; set; }

Check this MSDN page for further information

Upvotes: 3

Related Questions