LINQ SYNTAX

lulu50
lulu50 used Ask the Experts™
on
Hi,

This is my first linq and I'm not sure what I'm doing.

I need your help please.

I have to tables:

Table:
CABR_RuleIdentifier
RuleIdentifierId
RuleSetId
RuleIdentifier
IsActive


Table:
CABR_RuleDetail
RuleDetailId
isActive
RuleSetId
RuleIdentifierId
Version
Environment


I need to do this in linq:

SELECT RuleDetailId, isActive, RuleSetId, RuleIdentifierId, Version, Environment
FROM CABR_RuleDetail
LEFT JOIN CABR_RuleIdentifier
ON CABR_RuleDetail.RuleSetId = CABR_RuleIdentifier.RuleSetId; 


           CABR_RuleDetail RuleDetailResult = new CABR_RuleDetail();
            var result = from CABR_RuleDetail in RD
                         join CABR_RuleIdentifier in RI
                         on RD.RuleSetId equals RI.RuleSetId into ResultData
                         from RuleDetailResult in ResultData.DefaultIfEmpty()
                         select new
                         {
                             CABR_RuleDetail.RuleDescription,
                             CABR_RuleDetail.RuleDetailId,
                             CABR_RuleDetail.isActive,
                             CABR_RuleDetail.RuleSetId,
                             CABR_RuleDetail.RuleIdentifierId,
                             CABR_RuleDetail.Version,
                             CABR_RuleDetail.Environment,
                             CABR_RuleIdentifier.RuleIdentifier
                         };

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Eduard GherguArchitect - Coder - Mentor

Commented:
Hi,
Ok, what is the question?
lulu50Web application

Author

Commented:
Hi Eduard,

Thank you for helping me.

I want to create a left join linq

The syntax is wrong:

 CABR_RuleDetail RuleDetailResult = new CABR_RuleDetail();
            var result = from CABR_RuleDetail in RD
                         join CABR_RuleIdentifier in RI
                         on RD.RuleSetId equals RI.RuleSetId into ResultData
                         from RuleDetailResult in ResultData.DefaultIfEmpty()
                         select new
                         {
                             CABR_RuleDetail.RuleDescription,
                             CABR_RuleDetail.RuleDetailId,
                             CABR_RuleDetail.isActive,
                             CABR_RuleDetail.RuleSetId,
                             CABR_RuleDetail.RuleIdentifierId,
                             CABR_RuleDetail.Version,
                             CABR_RuleDetail.Environment,
                             CABR_RuleIdentifier.RuleIdentifier
                         };

Open in new window

Eduard GherguArchitect - Coder - Mentor

Commented:
Hi,

The query should look like:

var result = from RD in CABR_RuleDetail
                     join RI in CABR_RuleIdentifier
                     on RD.RuleSetId = RI.RuleSetId                      
                         select new
                         {
                             RD.RuleDescription,
                             RD.RuleDetailId,
                             RD.isActive,
                             RD.RuleSetId,
                             RD.RuleIdentifierId,
                             RD.Version,
                             RD.Environment,
                             RI.RuleIdentifier
                         };
Commented:
The problem is you won't be able to create a join for a non-existent RuleIdentifier, instead you probably want to do something like this:
var result = from CABR_RuleDetail in RD
             from CABR_RuleIdentifier in RI.Where(i => Equals(i.RuleSetId, CABR_RuleDetail.RuleSetId)).DefaultIfEmpty()
             select new
             {
                 CABR_RuleDetail.RuleDescription,
                 CABR_RuleDetail.RuleDetailId,
                 CABR_RuleDetail.isActive,
                 CABR_RuleDetail.RuleSetId,
                 CABR_RuleDetail.RuleIdentifierId,
                 CABR_RuleDetail.Version,
                 CABR_RuleDetail.Environment,
                 CABR_RuleIdentifier?.RuleIdentifier ?? "Whatever your default RuleIdentifier is"
             };

Open in new window

Proof of concept -
using System;
using System.Collections.Generic;
using System.Linq;

namespace EE_Q29167883
{
    class Program
    {
        static readonly List<Person> people = new List<Person>();
        static readonly List<Pet> pets = new List<Pet>();
        static readonly List<Food> dishes = new List<Food>();

        static void Main(string[] args)
        {
            people.AddRange(new[] {
                new Person { Id = 0, FirstName = "Magnus", LastName = "Hedlund" },
                new Person { Id = 1, FirstName = "Terry", LastName = "Adams" },
                new Person { Id = 2, FirstName = "Charlotte", LastName = "Weiss" },
                new Person { Id = 3, FirstName = "Arlene", LastName = "Huff" }, 
                new Person { Id = 4, FirstName = "Rui", LastName = "Raposo" }
            });

            pets.AddRange(new[] {
                new Pet { Id = 0, Name = "Barley", OwnerId = 1, FoodIds = new List<int> { } },
                new Pet { Id = 1, Name = "Boots", OwnerId = 1, FoodIds = new List<int> { 1, 5 } },
                new Pet { Id = 2, Name = "Whiskers", OwnerId = 2, FoodIds = new List<int> { 1, 4 } },
                new Pet { Id = 3, Name = "Blue Moon", OwnerId = 4, FoodIds = new List<int> { 0, 3 } },
                new Pet { Id = 4, Name = "Daisy", OwnerId = 0, FoodIds = new List<int> { 0, 3 } }
            });

            dishes.AddRange(new[] {
                new Food { Id = 0, Name = "Puppy Chow" },
                new Food { Id = 1, Name = "Fancy Feast" },
                new Food { Id = 2, Name = "Purina" },
                new Food { Id = 3, Name = "Beggin Strips" },
                new Food { Id = 4, Name = "Tuna" },
                new Food { Id = 5, Name = "Makeral" }
            });

            JoinExample();
            DifferentJoinExample();

            Console.ReadLine();
        }

        static void JoinExample()
        {
            var results = (from person in people
                           join pet in pets on person.Id equals pet.OwnerId
                           from foodId in pet.FoodIds
                           join dish in dishes on foodId equals dish.Id
                           select new
                           {
                               OwnerName = person.FirstName,
                               PetName = pet.Name,
                               Food = dish.Name
                           });

            Console.WriteLine("Join Example:");
            foreach (var item in results)
            {
                Console.WriteLine($"Owner: {item.OwnerName,-10}| Pet: {item.PetName,-10}| Food: {item.Food,-10}");
            }
            Console.WriteLine();
        }

        static void DifferentJoinExample()
        {
            var results = (from person in people
                           from pet in pets.Where(p => Equals(p.OwnerId, person.Id)).DefaultIfEmpty()
                           from dish in dishes.Where(d => pet?.FoodIds?.Any(i => Equals(i, d.Id)) ?? false).DefaultIfEmpty()
                           select new
                           {
                               OwnerName = person.FirstName,
                               PetName = pet?.Name ?? "NO PET",
                               Food = dish?.Name ?? "NO FOOD"
                           });

            Console.WriteLine("Different Join Example:");
            foreach (var item in results)
            {
                Console.WriteLine($"Owner: {item.OwnerName,-10}| Pet: {item.PetName,-10}| Food: {item.Food,-10}");
            }
            Console.WriteLine();
        }
    }

    class Person
    {
        public int Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

    class Pet
    {
        public int Id { get; set; }
        public int OwnerId { get; set; }
        public List<int> FoodIds { get; set; }
        public string Name { get; set; }
    }

    class Food
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }
}

Open in new window

Produces the following results -Capture.PNG-saige-
lulu50Web application

Author

Commented:
it_saige

Thank you so much!!!!

This is great!!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial