How to join two tables and fetch data from

Question

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; }

}

Why are you expecting more than one row when you're specifying `First()` ? — devlin carnate, May 20 '21 at 18:51
I have changed it to(.ToList()) now and I get all the data I wanted from one table. How can I join two tables and still use the where clause? — IT Forward, May 20 '21 at 18:56
Does this answer your question? [Join/Where with LINQ and Lambda](https://stackoverflow.com/questions/2767709/join-where-with-linq-and-lambda) — devlin carnate, May 20 '21 at 19:04
It starts with a class model including navigation properties. Don't use `join`. — Gert Arnold, May 20 '21 at 19:55

Blue · Accepted Answer · 2021-05-20T19:53:51.653

1

    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;}
}

edited May 20 '21 at 19:53

answered May 20 '21 at 19:12

Blue

163
1
12

Can you please put it in a method so that i can also get to see the return type which will hold the data? – IT Forward May 20 '21 at 19:17
I could hone in on what you're looking for better if you could share the two entity names that correspond to the tables you shared plus the fields you're looking to return – Blue May 20 '21 at 19:20
I have added the models of what I want to return. I just want to return everything, then from the front end will filter the data – IT Forward May 20 '21 at 19:24
Little unclear on what RatesheetModel expects, but this edit should get you closer – Blue May 20 '21 at 19:27
The function doesn't have to return a model, i have just put that model. here is the API which will consume the data. You can advise better on how i can do it public IActionResult GetRateSheet(int resellerId) { return Ok(rateSheetService.GetRateSheet(resellerId)); } – IT Forward May 20 '21 at 19:31
It should return a model - I wouldn't recommend returning an anonymous type if you can avoid it. – Blue May 20 '21 at 19:34
Thanks for the confirmation, can you please put every in one function for it to be more readable and learn from it too? Even if you don't fill the whole model, i will carry on – IT Forward May 20 '21 at 19:36
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/232662/discussion-between-blue-and-it-forward). – Blue May 20 '21 at 19:37

How to join two tables and fetch data from

1 Answers1