left join with up to one row in the right table in LINQ

Question

Hifriends, I have one customer table and one relationship table where I keep the customers I send survey link to. The same survey may have been sent to the same customer more than once. Or, some customers may not have sent any survey links. my goal is to bring all the customers once, and if some of these customers have been sent a questionnaire, I want to bring only the one which created date is biggest. The number of records must be equal to the number of customers. I wrote the query, but it is very difficult to translate linq or. Can you help.

this is query what I wrote

SELECT *
FROM dbo.Customer c
 LEFT JOIN dbo.SurveyCustomers sc ON sc.SurveyCustomerId =
(
SELECT A.SurveyCustomerId
FROM
(
    SELECT TOP 1 *
    FROM dbo.SurveyCustomers sc1
    WHERE sc1.STATUS = 1
          AND sc1.IsActive = 1
          AND sc1.CustomerId = c.CustomerId
          AND sc1.SurveyId = 1207
          AND sc1.STATUS = 1
          AND sc1.IsActive = 1
    ORDER BY sc1.CreatedDate DESC
) A
)
WHERE c.IsActive = 1
  AND c.STATUS = 1;

Customer table

CREATE TABLE [dbo].[Customer](
[CustomerId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[CustomerTitle] [varchar](500) NOT NULL,
[CustomerEmail] [varchar](500) NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[UpdatedDate] [datetime] NOT NULL,
[IsActive] [bit] NOT NULL,
[Status] [bit] NOT NULL)

SurveyCustomer

CREATE TABLE [dbo].[SurveyCustomers](
[SurveyCustomerId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[SurveyId] [int] NOT NULL FOREIGN KEY,
[CustomerId] [int] NOT NULL FOREIGN KEY,
[GuidId] [varchar](500) NOT NULL,
[CreatedDate] [datetime] NOT NULL,
[UpdatedDate] [datetime] NOT NULL,
[Status] [bit] NOT NULL,
[IsActive] [bit] NOT NULL)

Emiliano Javier González · Answer 1 · 2020-01-09T14:20:17.867

For this cases, GROUP BY clause is what you need:

SELECT c.SurveyCustomerId, MAX( /* THE MAX YOU WANT TO GET */)
FROM dbo.Customer c
 LEFT JOIN dbo.SurveyCustomers sc ON sc.SurveyCustomerId = A.SurveyCustomerId
GROUP BY c.SurveyCustomerId
WHERE c.IsActive = 1 AND c.STATUS = 1 /*AND OTHER CONDITIONS */;

Further reading on GROUP BY clasue:

https://learn.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver15

Update: Sorry missed the LINQ part: You should can do something like this:

public class SurveyCustomer
    {
        public int SurveyCustomerId { get; set; }

        public virtual ICollection<Survey> Surveys { get; set; }
    }

    public class Survey
    {
        public int SurveyId { get; set; }
    }

    public class SurveyCustomerReadModel
    {
        public int SurveyCustomerId { get; set; }

        public int LastSurveyId { get; set; }
    }

    public class SurveyCustomerRepository
    {
        public List<SurveyCustomer> SurveyCustomers { get; set; }

        public IEnumerable<SurveyCustomerReadModel> GetSurveyCustomerReadModel()
        {
            return this.SurveyCustomers
                .Select(sc => new SurveyCustomerReadModel
                {
                    SurveyCustomerId = sc.SurveyCustomerId,
                    LastSurveyId = sc.Surveys.Max(/* SOME CRITERIA FOR DEFINING THE MAX*/).SurveyId
                });
        }
    }

Regards!

I also edited the question. Sorry for this. I have forgotten — , Jan 09 '20 at 14:22

score 0 · Answer 2 · answered Jan 09 '20 at 14:14

0

You can do this:

var finalSurveyCustomers = from customer in Customers
                           join sc in SurveyCustomers on customer.CustomerId equals sc.SurveyCustomerId into leftCustomers
                           from leftCustomer in leftCustomers.DefaultIfEmpty()
                           select new { LeftId = customer.Id, SurveyId = leftCustomer ?? 0 }; // can use int.Max or int.Min

answered Jan 09 '20 at 14:14

Gauravsa

6,330
2
21
30

My brother I have editted the question. I am sorry for this. – Jan 09 '20 at 14:21

Serkan Arslan · Accepted Answer · 2020-01-09T15:25:33.577

0

You can use OUTER APPLY to getting the same result.

SELECT *
FROM dbo.Customer c
 OUTER APPLY 
(
    SELECT TOP 1 *
    FROM dbo.SurveyCustomers sc1
    WHERE sc1.STATUS = 1
          AND sc1.IsActive = 1
          AND sc1.CustomerId = c.CustomerId
          AND sc1.SurveyId = 1207
    ORDER BY sc1.CreatedDate DESC
) A
WHERE c.IsActive = 1
  AND c.STATUS = 1;

and linq equivalent:

Customers.SelectMany(c => SurveyCustomers
                        .Where(sc1 => 
                            sc1.Status == true 
                            && sc1.IsActive == true
                            && sc1.CustomerId == c.CustomerId
                            && sc1.SurveyId == 1207 )
                        .OrderByDescending(sc1 => sc1.CreatedDate)
                        .Take(1).DefaultIfEmpty(), 
                    (c, sc) => new { c, sc })
.ToList()

edited Jan 09 '20 at 15:25

answered Jan 09 '20 at 14:24

Serkan Arslan

13,158
4
29
44

thanks bro, I have never used outer apply before. I loved it a lot. But linq equivalent is getting an error in where clause – Jan 09 '20 at 14:39
Can you write the exception message? – Serkan Arslan Jan 09 '20 at 14:42
I changed SurveyCustomers -> entity.SurveyCustomers.No problem now. I will try immediately and accept the answer. Çok teşekkür ederim:) – Jan 09 '20 at 14:48
I have tried sql is working very well like what I want. But linq part didn't bring right null record. – Jan 09 '20 at 14:55
thank you so much. That's exactly what I wanted.I accept the answer. You can also updown the question if you like. – Jan 10 '20 at 05:20

left join with up to one row in the right table in LINQ

3 Answers3