locked
LINQ Get joined objects or null if not RRS feed

  • Question

  • User-1512739781 posted

    I have two tables :

    class Cat {
        [Key]
        public Guid Id { get; set; }
        public string Name { get; set; }
    }
    
    class Dog {
        [Key]
        public Guid Id { get; set; }
        public virtual Cat Kitty { get; set; }
        public string Name { get; set; }
    }

    and a view model

    class ResultViewModel
    {
        public Guid Id { get; set; }
        public Guid DogId { get; set; }
        public string Name { get; set; }
        public string DogName { get; set; }
    }

    What I'd like to do looks something like :

    async Task<List<ResultViewModel>> GetCatsAndDogs()
    {
        return await (
            from cat in dbContext.Cats
            from dog in dbContext.Dogs
            where cat.Id == dog.Kitty.Id
            select new ResultViewModel {
                Id = cat.Id,
                Name = cat.Name,
                DogId = (dog == null) ? Guid.Empty : dog.id,
                DogName = (dog == null) ? string.Empty : dog.Name
            }
        )
        .ToListAsync()
    }

    What I can't figure out is how to get instances of Cat where there are no corresponding instances of Dog.

    Please help :)

    Thursday, December 14, 2017 6:16 AM

Answers

  • User-1512739781 posted

    As with most everything, as soon as I ask the question, I found the solution on my own. While I'm not 1000% sure I did it correctly, I'll simply explain what I Googled to figure it out.

    I learned that this type of a relationship is known as a "left outer join" and the solution I made was something like the following :

    async Task<List<ResultViewModel>> GetCatsAndDogs()
    {
        return await (
            from cat in dbContext.Cats
            join matchingDog in dbContext.Dogs
                on cat.Id equals matchingDog.Id
                into joinTable
                from dog in joinTable.DefaultIfEmpty()
            select new ResultViewModel {
                Id = cat.Id,
                Name = cat.Name,
                DogId = (dog == null) ? Guid.Empty : dog.id,
                DogName = (dog == null) ? string.Empty : dog.Name
            }
        )
        .ToListAsync()
    }
    

    I don't know for sure whether this is the correct solution to the problem, but it appears to work for me. 

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 14, 2017 8:40 AM