0

I'm using asp.net web forms 4.5

I'm trying to join two tables products and lots by

var query = _cc.products.Join(_db.lots,
                           p => p.parentLotIndex,
                           l => l.idx,
                           (p, l) => new {P = p, L = l});
int prodCount = query.Count();

Edit : This is many to 1 relationship. That is, there could be numbers of products related to a single lot. So the query is to find how many products are there in a given lot.

But this is giving me a weird error something like..

NotSupportedException was unhandled by user code.
The specified LINQ expression contains references to queries that are associated with different contexts.

And I don't know what this means. Please someone help me? Thanks in advance.

PS : by the way, executing the sql query in SQL management studio gave me the correct answer.. with standard sql queries.. so I guess I'm doing something wrong with linq.

Jinmo Chong
  • 91
  • 1
  • 12
  • Did you google it? Maybe its not releated but: https://stackoverflow.com/questions/7332920/error-the-specified-linq-expression-contains-references-to-queries-that-are-as – Nekeniehl Dec 13 '17 at 13:09
  • Do you not have a Navigation property that you can use instead? Also if it's a 1-to-1 relationship then why do you need the join? Or is it actually a 1 to 0 or 1 relationship? – juharr Dec 13 '17 at 13:09
  • @juharr Good point. I guess I'm not familiar with these naming, so the product could not have been made so I'm trying to find out about that, so I guess it would be 0 to 1 relationship and I'm trying to count the products made – Jinmo Chong Dec 13 '17 at 13:11
  • 2
    If you want all products that have a lot then something like this might work (guessing at your Navigation property name) `_db.products.Where(p => p.Lot != null).Select(p => new { P = p, L = p.Lot})`. – juharr Dec 13 '17 at 13:13
  • @Nekeniehl Of course I googled it. tolist or toarray does not work again, showing the same error. Funny thing is though, when I put toarray or tolist, the exception comes from the sql query, not the count. – Jinmo Chong Dec 13 '17 at 13:14
  • Usually there is a one to many relationship between products and lots as the same product can be made several times. – Olivier Jacot-Descombes Dec 13 '17 at 13:15
  • @JinmoChong The exception will occur when you materialize the query. `ToList` and `Count` or a `foreach` will cause the query to be materialized (i.e. run the SQL against the DB and return the results). – juharr Dec 13 '17 at 13:16
  • I think is the .Select what you are looking for. Linq is sometimes confusing, try to use the old way with a for loop and check if you get the error. – Nekeniehl Dec 13 '17 at 13:16
  • @juharr That's a great way of thinking. I only thought of joining it. Although, product is the one who cannot be created and also, it only has the lotIndex, not the lot itself, so cannot use p.Lot in this case, I think – Jinmo Chong Dec 13 '17 at 13:17
  • @JinmoChong If you don't have a navigation property then either the Entities were not created properly or your DB does not have a foreign key relationship between the tables. I'd suggest fixing those issues if they exist. – juharr Dec 13 '17 at 13:19
  • @juharr I didn't designed this db and frankly a little reluctant to touch the db itself so I think I'm just gonna try using queries instead. – Jinmo Chong Dec 13 '17 at 13:22
  • @juharr Oh I just realized this is not a 1 to 1 relationship and it is many to 1 relationship. Sorry about that. Not too great about databases. – Jinmo Chong Dec 13 '17 at 13:26
  • Just to be clear your code doesn't really match up with that exception. I would have expected something like `_db.products.Join(_anotherDB.lots...` so if you are actually using the same context for both table entities then I'm really not sure why you're getting that error. – juharr Dec 13 '17 at 13:29
  • Oh.. That's exactly right!!! I actually refactored the code to look better.. I'll fix that. I didn't know it was the problem – Jinmo Chong Dec 13 '17 at 13:31

1 Answers1

1

There are 2(!!!) contexts in your code:

  1. _cc.products

  2. _db.lots

And error message telling you this.

Hemid Abbasov
  • 175
  • 2
  • 5