Linq Join query returning empty dataset-CodePudding

I am using below code to join two tables based on officeId field. Its retuning 0 records.

  IQueryable<Usage> usages = this.context.Usage;
  var  agencyList = this.context.Agencies.ToList();
  var data = usages.ToList();
  var ms =  data.Join(agencyList,
                                     r => r.OfficeId,
                                     a => a.OfficeId,
                                     (r, a) => new UsageAgencyApiModel () { 
                                            Id = r.Id,
                                            Product = r.Product,
                                            Chain = a.Chain,
                                            Name = a.Name
                                     }).ToList();

I have 1000 records in agencies table and 26 records in usage table.

I am expecting 26 records as a result with chain and name colums attached to result from agency table.

Its not returning anything. I am new to .net please guide me if I am missing anything

CodePudding user response：

One problem ist that you load all into memory first(ToList()).

With joins i prefer query syntax, it is less verbose:

var query = from a in this.context.Agencies
            join u in this.context.Usage on a.OfficeId equals u.OfficeId
            select new UsageAgencyApiModel() 
            { 
               Id = u.Id,
               Product = u.Product,
               Chain = a.Chain,
               Name = a.Name
            };

List<UsageAgencyApiModel> resultList = query.ToList();

CodePudding user response：

The following code can help to get the output based on the ID value.

Of course, I wrote with Lambda.

var officeId = 1;
var query = context.Agencies    // your starting point - table in the "from" statement
   .Join(database.context.Usage, // the source table of the inner join
      agency => agency.OfficeId,        // Select the primary key (the first part of the "on" clause in an sql "join" statement)
      usage  => usage.OfficeId ,   // Select the foreign key (the second part of the "on" clause)
      (agency,  usage) => new {Agency = agency, Usage = usage }) // selection
   .Where(x => x.Agency.OfficeId == id);    // where statement