CODEWITHSUNDEEP
c#asp.net-mvcentity-frameworklambdarepository-pattern
Jhensen
Jhensen

Reputation: 81

Searching data from multiple tables using Entity framework, Lambda Expressions and Repositorys pattern

I have the InfoAppointment class, and that class has the foreign key Boat_Id of the Boat class in its table. I would like to show all records of class InfoApontamento that contains a particular Boat. How would I use the foreign key of the boat Class that is in the infoAppointment table to find a particular boat, using lambda expressions and repositorys? Its a One To many relationship

public  class InfoAppointment : Entity
{      
    public string Status { get; set; }

    public string Code{ get; set; }

    public string Observation{ get; set; }

    public virtual Boat Boat { get; set; }
}

public class Boat : Entity
{
    public Guid BoatId { get; set; }
    public virtual InfoAppointment  InfoAppointment { get; set; }
}

My Example repository

public virtual IEnumerable<TEntity> Search(Expression<Func<TEntity, bool>> predicate)
{
    return Dbset.Where(predicate);
}

public class InfoAppointment Repository : Repository<InfoAppointment >, IInfoAppointmentRepository
{
    public InfoAppointmentRepository(RveContext rveContext) : base(rveContext)
    {
    }

    public InfoAppointment GetBoatByName(string boatName)
    {
        // HOW TO GET THE BOATS USING THE FK
    }

    public InfoAppointment GetByCode(string code)
    {
        return Search(c => c.active && !c.excluded && c.Code== code).FirstOrDefault();
    }
}

Upvotes: 0

Views: 487

Answers (3)

Miq
Miq

Reputation: 4289

I think the problem is in your relation. Boat class should have a list of InfoAppointment, not single object.
I don't see the case, why you have a boat that can have only one appointment.

However if it's still the case, simply query (c => c.Boat?.Name == name), lazy loading will do the rest.

Edit: As for defining foreign key in your DB model use [ForeignKey("BoatId")] annotation as described here: http://www.entityframeworktutorial.net/code-first/foreignkey-dataannotations-attribute-in-code-first.aspx

So your InfoAppointment class should look like this:

public  class InfoAppointment : Entity
{      
public string Status { get; set; }

public string Code{ get; set; }

public string Observation{ get; set; }

public int BoatId { get; set; }

[ForeignKey("BoatId")]
public virtual Boat Boat { get; set; }
}

Upvotes: 0

Miamy
Miamy

Reputation: 2299

You can try:

public InfoAppointment GetByBoatName(string name) // this name is more semantically correct
{
    return Dbset.FirstOrDefault(c => c.Boat != null && c.Boat.Name == name);
}

And you don't need to use Where before FirstOrDefault - the last can accept predicates too, so you can modify GetByCode method (or just use FirstOrDefault in the Search method).

Edit:

For the case when a boat has several appointments. I suggest you really have the next definition:

public class Boat : Entity
{
  public Guid BoatId { get; set; }
  public virtual List<InfoAppointment> InfoAppointments { get; set; }
}

To return all appointments for the selected boat:

public List<InfoAppointment> GetAllByBoatName(string name)
{
  return Boats.First(c => c.Name == name).InfoAppointments.ToList();
}

To return one appointment for the selected boat:

public InfoAppointment GetOneByBoatName(string name, 
    Expression<Func<TEntity, bool>> predicate)
{
  return Boats.First(c => c.Name == name).InfoAppointments.FirstOrDefault(predicate);
}

Upvotes: 1

Manoj Choudhari
Manoj Choudhari

Reputation: 5634

You can use .Include syntax.

Please refer blog at : http://foreverframe.net/when-use-include-with-entity-framework/

Code:

var author = context.Boat.Include(a => a.InfoAppointment).FirstOrDefault(c=> c.Boat != null c.Boat.Name == <<name>>);

Upvotes: 0

Related Questions