LINQ C# – API CONTROLLER – Evaluate query

March 22, 2022

I have a table called "Cowork" and another called "Commune". In a controller I receive a parameter called NameCommune, given that value I need to find the ID that matches the received parameter, the result of the above I need to evaluate if that ID exists in the "COWORK" table. (THE TWO TABLES ARE RELATED) I’m new to using LINQ, any ideas for this? I have tried the following, but it returns an empty [ ]

public  IActionResult GetNearbyPlaces(string nameComuna)
        {
            IQueryable<Commune> queryCommune = _context.Commune;
            IQueryable<Cowork> queryCowork = _context.Cowork;

            var codeCommune = (from code in queryCommune where code.name == nameComuna select code.code);
            
            var coworkList = (from c in _context.Cowork where c.commune_code == codeCommune.ToString() select c).ToList();

            return Ok(coworkList); // This returns an empty [ ]
        }

In my common table the ID or my primary key is represented by the name code.

>Solution :

You probably want something like this:

public IActionResult GetNearbyPlaces(string nameComuna)
{
    IQueryable<Commune> queryCommune = _context.Commune;
    IQueryable<Cowork> queryCowork = _context.Cowork;

    var query =
        from code in queryCommune
        where code.name == nameComuna
        join c in _context.Cowork on code.code equals c.commune_code
        select c;

    return Ok(query.ToList());
}

Or possibly:

public IActionResult GetNearbyPlaces(string nameComuna)
{
    IQueryable<Commune> queryCommune = _context.Commune;
    IQueryable<Cowork> queryCowork = _context.Cowork;

    var query =
        from c in _context.Cowork
        join code in queryCommune.Where(x => x.name == nameComuna)
            on c.commune_code equals code.code into codes
        where codes.Any()
        select c;
        
    return Ok(query.ToList());
}