CODEWITHSUNDEEP
asp.netlinqentity-framework-5
Steve Hougom
Steve Hougom

Reputation: 11

Linq. Anonymous type error when joining to multiple tables

Im trying to return an IQueryable based on my model.

But I need to join to the same lookup table twice. Then return the query variable to the gridview. 

public IQueryable<Benchmark> GetBenchMarks([QueryString("hydrant")] string hydrant,
[QueryString("revdate")] string revdate, [QueryString("street")] string street,
[QueryString("quadrant")] string quadrant, [QueryString("desc")] string desc) {

    IQueryable<Benchmark> query = from p in _db.Benchmarks
    join s in _db.Streets on p.Street1Number equals s.Id
    join s2 in _db.Streets on p.Street2Number equals s2.Id
    select new {
        Street1Name = s.StreetName,
        p.OrderNumber,
        p.HydrantNumber,
        Street2Name = s2.StreetName,
        p.RevisionDate,
        p.Quadrant,
        p.Description,
        p.Street1Number
    };
}

So there is a red squiggle line on the 2nd join to s2. And the following error.

Error 5 Cannot implicitly convert type 'System.Linq.IQueryable<AnonymousType#1>' to 'System.Linq.IQueryable<Benchmarks.Model.Benchmark>'. An explicit conversion exists (are you missing a cast?) C:\Projects\Benchmarks\Benchmarks\Benchmarks_Home.aspx.cs 63 25 Benchmarks

Upvotes: 1

Views: 894

Answers (2)

mipe34
mipe34

Reputation: 5666

The result of your query is IEnumerable of anonymous objects, thus it cannot be converted to Benchmark.

If you want to set some additional properties (Street1Name - that are evidently not mapped on DB) from joined relations you can do: 

 IQueryable<Benchmark> query = from p in _db.Benchmarks
            join s in _db.Streets on p.Street1Number equals s.Id
            join s2 in _db.Streets on p.Street2Number equals s2.Id
        select new { 
                 ....
        };
 var ex = query.ToList();
 var result = new List<Benchmark>();
 foreach(bn in ex){
      result.Add(new Benchmark{ OrderNumber = bn.OrderNumber .... });
 }
// return result.AsQueryable();   
// but now it losts the point to return it as queryable, because the query was already executed so I would simply reurn that list
 return result;

Another option is to make new class representing the object from the query and return it from the method like:

... select new LoadedBenchmark { Street1Name = s.StreetName ....}

Upvotes: 0

yoozer8
yoozer8

Reputation: 7489

Since you end your query with select new {...}, you are creating an anonymous object for each result. Instead, use select p, and each result will be a Benchmark.

However, it looks like returning a Benchmark is not what you want. In this case, you would want to change query to be of type IQueryable or IQueryable<dynamic> (and probably change the return type of the GetBenchMarks function as well, unless it does return IQueryable<Benchmark>!).

A second (potentially better) alternative would be to create a class to represent this anonymous type, and use that.

Upvotes: 2

Related Questions