How can I get query results from lambda linq from two tables with join

Question

I am trying to make a lambda linq query with join to two tables but I cannot make it work. I already have the linq query

timologia = db.Timologio.Where(p => (p.Imerominia >= apoDate.Date && p.Imerominia <= 
            eosDate.Date.AddDays(1)) &&
            (p.Xrisi == xrisi && p.Diasafistis == diasafistis) &&
            (!p.IsYpodeigma.HasValue || !p.IsYpodeigma.Value)
            .OrderByDescending(p => p.Imerominia).ToList();

now I want to put another variable but I must read it from another table.

I want to search for timologio.seiraId which seiraId is in the table SeiresTimologiou and from there I want to get the EmfanisiStoMydata which is a bit.

In SQL management I can get the results that I want with this code:

select g.Description, t.*
                from Timologio t
                join SeiraTimologiou g on g.Id = t.SeiraId
                where g.EmfanisiStoMydata is not null

I found in stackoverflow this answer https://stackoverflow.com/a/2767742/7054211

but when I try to use it throws me an error

   var query = db.Timologio 
               .Join(db.SeiraTimologiou,
               timol => timol.SeiraId,
               seira => seira.EmfanisiStoMydata
               (timol, seira) => new { Timol = timol, Seira = seira })
               .Where(previous query && (TimolAndSeira=> TimolAndSeira.SeiraTimologiou.EmfanisiStoMydata == 1 ));

The error that I have is

The type arguments for method 'Queryable.Join<TOuter, TInner, TKey, TResult>(IQueryable, IEnumerable, Expression<Func<TOuter, TKey>>, Expression<Func<TInner, TKey>>, Expression<Func<TOuter, TInner, TResult>>)' cannot be inferred from the usage. Try specifying the type arguments explicitly.

Can someone help me?

C# is a language of types. You have `timol => timol.SeiraId` - what type is `SeiraId`? Then you have `seira => seira.EmfanisiStoMydata` - why type is `EmfanisiStoMydata`? Are they the same? The error shows the third and fourth arguments must both return the same type `TKey`. You should have `seira => seira.SeiraId` instead (or maybe `seira => seira.Id` - it is hard to say since you didn't provide any classes). Your `Where` is also very wrong, but you didn't put real code there. — NetMage, Mar 28 '23 at 23:36
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. — NetMage, Mar 28 '23 at 23:38

score 0 · Accepted Answer · answered Mar 28 '23 at 06:55

I finally found the correct query, so the ling lambda query that works is:

var result = db.Timologio
                .Join(db.SeiraTimologiou, t => t.SeiraId, g => g.Id, (t, g) => new { t, g })
                .Where(x => x.g.EmfanisiStoMydata != null)
                .Select(x => new 
                { 
                    x.g.Description,
                    x.t 
                });

Marko Coetzee · Answer 2 · 2023-03-27T12:58:05.687

-1

The generics in the Join method could not be inferred by the compiler. To fix this, just add the types that you are trying to join.

var query = db.Timologio
            .Join<Timologio, SeiraTimologiou>(
            timol => timol.SeiraId,
            seira => seira.EmfanisiSoMyData
            )

I would suggest doing your join using linq's query syntax, though. It's similar to Sql and has cleaner syntax. e.g.

from t in db.Timologio
    join g in db.SeiraTimologiou 
        on t.SeiraId equals g.Id
where g.EmfanisiStoMydata != null
select new {
    Description = g.Description,
    Timologio = t
}

edited Mar 27 '23 at 12:58

answered Mar 27 '23 at 11:13

Marko Coetzee

44
4

1

My problem is in linq quey and not the sql query. The sql query is working great. – rippergr Mar 27 '23 at 11:55
Pardon, I misread the question. I've edited the answer accordingly. – Marko Coetzee Mar 27 '23 at 12:58
The lambda linq query doesn't work. It throws error that table does not contain a definition for 'Join' and no accesible exension method 'Join' accepting a first argument of type 'Table' – rippergr Mar 28 '23 at 05:49
Of the types can't be inferred by the compiler, that means there is a type error. Adding explicitly types is never the right answer. – NetMage Mar 28 '23 at 23:32

How can I get query results from lambda linq from two tables with join

2 Answers2