CODEWITHSUNDEEP
c#entity-frameworkjoin
IT Forward
IT Forward

Reputation: 395

How to join two tables and fetch data from

I have two tables from which I want to fetch the data and return it to the API for consumable purposes. There is a relationship between the two tables.

[![enter image description here][1]][1]

When I try to fetch the data, it is returning only one row which is not what I want.

How can I return all the data related to ResellerId (8435 examples)?

This is my code:

    public RateSheetModel GetRateSheet(int resellerId)
    {
        // This only returns only one row.
        // How can I get all rows for all the same Id?
        var rateSheetDetails = (from r in _Context.WholesaleRateSheet 
                                where r.ResellerId == resellerId 
                                select r).First();
    }

Models

public class WholesaleRateSheetMarkup
{
    [Key]
    public int RateSheetMarkupId { get; set; }
    [Required]
    public int ResellerId { get; set; }
    [StringLength(50)]
    public string RatesheetName { get; set; }

} 

public class WholesaleRateSheet
{
    [Key]
    public int RateSheetId { get; set; }
    [Required]
    public int RateSheetMarkupId { get; set; }
    [Required]
    public int ResellerId { get; set; }
    public string CountryCode { get; set; }
    public string Description { get; set; }
    public decimal Peak { get; set; }
    public bool IsSouthAfricanRate { get; set; }
    public bool IsInertnationRate { get; set; }
    public bool IsSpecificRate { get; set; }
    public int DestinationGroupSetId { get; set; }
    public int DestinationGroupId { get; set; }
    public string DestinationLookup { get; set; }
    public DateTime CreatedDate { get; set; }
    public string CreatedByUsername { get; set; }
    public DateTime LastUpdatedDate { get; set; }
    public string UpdatedByUsername { get; set; }

}

Upvotes: 0

Views: 145

Answers (1)

Blue
Blue

Reputation: 217

    var rateSheetDetails = (from r in _Context.WholesaleRateSheet 
                            join rateSheet in _Context.<tableName> on r.ResellerId equals rateSheet.ResellerId
                            where r.ResellerId == resellerId 
                            select new { foo });

You're making a new type by doing this, as a combination of data from both tables. You can either define a model class and use something like

select new MyClass {
  Foo = r.RateSheetMarkupId,
  Bar = rateSheet.RateSheetName
}

Otherwise, no class definition is required. Simply using 'new' without a class will create an anonymous type with properties that match what you're selecting. The example here could be more precise if you share the entity names.

Altogether, going off your code:

    public List<RateSheetModel> GetRateSheet(int resellerId)
    {
        var rateSheetDetails = (from r in _Context.WholesaleRateSheet
                                join m in _Context.RateSheetMarkup on r.ResellerId equals m.ResellerId
                                where r.ResellerId == resellerId 
                                select new RateSheetModel  {
                                    ResellerId = r.ResellerId
                                    RatesheetName = m.RatesheetName
                                };
        return rateSheetDetails.ToList<RateSheetModel>;


    }
    
public RateSheetModel  { //dunno what this model looks like, but example
   public int ResellerId {get; set;}
   public string RatesheetName {get; set;}
}

Upvotes: 1

Related Questions