Linq Join query returning empty dataset

user_6603094 0
Viewed 56

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

  IQueryable<Usage> usages = this.context.Usage;
  usages = usages.Where(usage => usage.OfficeId == officeId);

  var  agencyList = this.context.Agencies.ToList();
  var usage = usages.ToList();
  var query =  usage.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

EDIT

@Tim Schmelter's solution works fine if I get both table context while executing join. But I need to add filter on top of usage table before applying join

 IQueryable<Usage> usages = this.context.Usage;

usages = usages.Where(usage => usage.OfficeId == officeId);

 
            var query = from a in usages 
           // works with this.context.usages instead of usages
            join u in this.context.Agencies on a.OfficeId equals u.OfficeId
            select new  
            { 
               Id = a.Id,
               Product = a.Product,
               Chain = u.Chain,
               Name = u.Name
            };
return query.ToList();

Attaching screenshot here

enter image description here

same join query works fine with in memory data as you see below

enter image description here

Both ways works fine if I add in memory datasource or both datasource directly. But not working if I add filter on usages based on officeId before applying join query

linq .net c#
2 Answers

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();

Edit: You should be able to apply the Where after the Join. If you still don't get records there are no matching:

var query = from a in this.context.Agencies
            join u in this.context.Usage on a.OfficeId equals u.OfficeId
            where u.OfficeId == officeId
            select new UsageAgencyApiModel{ ... };
user_2842400

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
user_124926420
Related