-
Dec 29th, 2020, 03:12 AM
#1
Thread Starter
New Member
LINQ join table with parameter
Hi,
I have 2 tables with one relation "BoekID"
But a book belongs to a library "GemeenteID" This is defined as a parameter IDGemeente.
In SQL is the query: (and this query works)
Code:
SELECT Boeken.BoekID, Boeken.ISBN, Boeken.Titel, Boeken.Subtitel, Boeken.Druk, Boeken.Serie, Boeken.Deel, Boeken.AantalPaginas, Boeken.Verschijningsvorm, Boeken.Verschijningsdatum, Boeken.KorteInhoud
FROM Boeken INNER JOIN
BoekenGemeenten ON BoekenGemeenten.BoekID = Boeken.BoekID AND BoekenGemeenten.GemeenteID = @Param1
How can I do this in LINQ ?
I tried this code but it gives an error.
Code:
Dim zoekBoeken = From id In dbBoeken.Boekens
Join id2 In dbBoekenGemeenten.BoekenGemeentens On id2.BoekID Equals id.BoekID And
id2.GemeenteID Equals IDGemeente
Select id
Kind regards,
Dante
Last edited by havenaardante; Dec 29th, 2020 at 04:18 AM.
-
Dec 29th, 2020, 06:21 AM
#2
Re: LINQ join table with parameter
What's the error? It looks OK at a glance.
The only difference I notice is that you're checking the GemeenteID in the join rather than in a where clause. Is there some reason for that?
Other than that, where is IDGemeente coming from? I assume it's a variable but can't see it in your code.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Dec 29th, 2020, 09:10 AM
#3
Thread Starter
New Member
Re: LINQ join table with parameter
Hi FunkyDexter,
Thanks for your reaction.
The error is:
BC36622: You must reference at least one range on both sides of the Equals operator. Range variable(s) 'id' must appear on one side of the 'Equals' operator, and range variable(s) 'id2' must appear on the other.
IDGemeente is indeed a variable.
I have to JOIN BoekenGemeenten because the BoekID is in there so I can't use a WHERE clause
However, IDGemeente does not exist in the table Boeken. I need to use the variable IDGemeente because i will check if the book belongs to the library.
Kind regards,
Dante
Last edited by havenaardante; Dec 29th, 2020 at 09:13 AM.
-
Jan 3rd, 2021, 12:17 PM
#4
Re: LINQ join table with parameter
You must reference at least one range on both sides of the Equals operator.
I believe that's telling you that you have an Equals clause in the Join that only has a range variable on one side of it. See the docs here.
Unfortunately I don't have a development machine with me and it's been a couple of years since I used LinqToSQL so take this with a pinch of salt but the only part of your query that doesn't meet that description is the comparison to IDGemeente.
so I can't use a WHERE clause
I don't think that's correct. The Join is resolved before the Where clause and anything produced by the Join is available as a criteria to the Where. So I think this should work:-
Code:
Dim zoekBoeken = From id In dbBoeken.Boekens
Join id2 In dbBoekenGemeenten.BoekenGemeentens On id2.BoekID Equals id.BoekID
Where id2.GemeenteID Equals IDGemeente
Select id
Like I say, though, it's a while since I used LinqToSQL so I'm not confident. If I was you I'd try the above without the Where clause first. It should return all records. Assuming that works, add the Where clause back in but use a hard coded value rather than a variable. Finally add the variable back in.
The best argument against democracy is a five minute conversation with the average voter - Winston Churchill
Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd
-
Jan 10th, 2021, 11:49 PM
#5
Member
Re: LINQ join table with parameter
Please try this query, To LINQ join table with parameter
Code:
var partialResult = (from c in db.Customers
join o in db.Orders
on c.customer_id equals o.customer_id
select new
{c.name,
o.order_total,
o.order_date }).OrderBy(m => m.order_date.Month).ThenBy(y => y.order_date.Year);
var finalResult = from c in db.Customers
orderby c.name
select new
{
name = c.name,
list = (from r in partialResult where c.name == r.name select r.order_total).ToList()
};
foreach (var item in finalResult)
{
Console.WriteLine(item.name);
if (item.list.Count == 0)
{
Console.WriteLine("No orders");
}
else
{
foreach (var i in item.list)
{
Console.WriteLine(i);
}
}
}
I hope this query will be useful to you.
Thank you.
< advertising removed by moderator >
-
Jan 31st, 2021, 04:20 AM
#6
Re: LINQ join table with parameter
Originally Posted by havenaardante
Code:
SELECT Boeken.BoekID, Boeken.ISBN, Boeken.Titel, Boeken.Subtitel, Boeken.Druk, Boeken.Serie, Boeken.Deel, Boeken.AantalPaginas, Boeken.Verschijningsvorm, Boeken.Verschijningsdatum, Boeken.KorteInhoud
FROM Boeken INNER JOIN
BoekenGemeenten ON BoekenGemeenten.BoekID = Boeken.BoekID AND BoekenGemeenten.GemeenteID = @Param1
For queries like that, I tend to use an "... Where SomeFieldValue In () ..." construction instead of a Join.
E.g. something like that:
Code:
SELECT BoekID, ISBN, Titel, Subtitel, Druk, Serie, Deel, AantalPaginas, Verschijningsvorm, Verschijningsdatum, KorteInhoud
FROM Boeken
Where BoekID In (Select BoekID From BoekenGemeenten Where GemeenteID = @Param1)
Much nicer to read - equally well optimizable by the SQL-interpreter - and probably easier to formulate as a Linq-expression as well.
Olaf
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|