Results 1 to 6 of 6

Thread: LINQ join table with parameter

  1. #1

    Thread Starter
    New Member
    Join Date
    Jun 2020
    Posts
    3

    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
    Name:  Tables.PNG
Views: 222
Size:  15.6 KB

    Kind regards,
    Dante
    Last edited by havenaardante; Dec 29th, 2020 at 04:18 AM.

  2. #2
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    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

  3. #3

    Thread Starter
    New Member
    Join Date
    Jun 2020
    Posts
    3

    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.

  4. #4
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    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

  5. #5
    Member
    Join Date
    Jul 2019
    Location
    Ahmedabad
    Posts
    57

    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 >

  6. #6
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: LINQ join table with parameter

    Quote Originally Posted by havenaardante View Post
    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
  •  



Click Here to Expand Forum to Full Width