Thank you for always taking care of me.
I want to get the same results as the SQL below on LINQ, but I'm struggling because I can't meet my expectations.
I tried to create it based on the information here, but the difference is that there are three partitions, and I expect that there is a mistake in the nested part of foreach, but I have not been able to get the expected value.
What do you want to know?
Is it possible to use a compound key with ROW_NUMBER()OVER(PARTMENT BY) in LINQ?
After the LEFT JOIN, the grouping is done in SQL, so I'm doing it in that order, but is it correct?
Is the nesting of foreach correct?
Yes.
I would appreciate it if you could teach me
TMP1
ID int
START_DATE datetime
TOUROKU_NO INT
CD string
TMP2
ID int
START_DATE datetime
TOUROKU_NO INT
CD string
END_DATE datetime
SELECT
BB4.ID,
BB4. [START_DATE],
BB4. [TOUROKU_NO],
MAX (CASE WHEN BB4. [RN] = 1 THEEN BB4. [CD] ELSE' 'END) AS [CD],
MAX (CASE WHEN BB4. [RN] = 1 THEEN BB4. [END_DATE] ELSE' 'END) AS [END_DATE]
FROM(
SELECT
*,
ROW_NUMBER() OVER (Partition BY BB3. [ID], BB3. [START_DATE], BB3. [TOUROKU_NO] ORDER BY BB3. [END_DATE] AS [RN]
FROM(
SELECT
BB1.*,
CASE WHEN BB2. [START_DATE] is NULL THE GETDATE() ELSE BB2. [START_DATE] ENDAS [END_DATE]
FROM# TMP1 AS BB1
LEFT JOIN# TMP1AS BB2 ON (BB1.[ID] = BB2.[ID]AND BB1.[TOUROKU_NO] = BB2.[TOUROKU_NO]AND BB1.[START_DATE]<BB2.[START_DATE])
AS BB3
AS BB4
GROUP BY BB4. [ID], BB4. [START_DATE], BB4. [TOUROKU_NO]
//Self-coupling
var joinTable=from a in TMP1
join a2 in TMP1
on a.ID equals a2.ID intog
from a2ing.DefaultIfEmpty()
where a.TOUROKU_NO==a2.TOUROKU_NO&a.START_DATE<a2.START_DATE
select new {
a.ID,
a. START_DATE,
a. TOUROKU_NO,
a.CD,
END_DATE=(DBNull.Value.Equals(a2.START_DATE)?(DateTime)a2.SERVERDATE:(DateTime)a2.START_DATE)
};
// Grouping
var groupTable = from a in joinTable
group by new {a.ID, a.START_DATE, a.TOUROKU_NO} intog
select new {
ID = from Ming
select m.ID,
START_DATE = g.Key.START_DATE,
TOUROKU_NO=g.Key.TOUROKU_NO,
CD = from Ming
select m.CD,
END_DATE=fromming
orderbym.END_DATE
select m.END_DATE
};
foreach (varitem in groupTable) {
foreach(varid in item.ID) {
foreach(varcd in item.CD){
foreach(var end_date in item.END_DATE){
varRow=DataTable.NewRow();
Row[0] = id.ToString();
Row[1] = DatePicker(item.START_DATE.ToString());
Row[2] = Convert.ToInt32 (item.TOUROKU_NO);
Row[3] = cd.ToString();
Row[4] = DatePicker(end_date.ToString());
DataTable.Rows.Add (Row);
}
}
}
}
Additional information
SELECT
BB1.*,
CASE WHEN BB2. [START_DATE] is NULL THE GETDATE() ELSE BB2. [START_DATE] ENDAS [END_DATE]
FROM TMP1 AS BB1
LEFT JOIN TMP1 AS BB2 ON (BB1.[id] = BB2.[id] AND BB1. [TOUROKU_NO] = BB2. [TOUROKU_NO] AND BB1. [START_DATE] <BB2. [START_DATE])
ID STARTDATE TOROKU_NO CD END_DATE
7 2010-06-30 00:00:00.000 1 03010, 2011-07-05 00:00:00.000
7 2010-06-30 00:00.000 1 03010, 2011-09-26 00:00:00.000
7 2010-06-30 00:00:00.000 1 03010, 2011-12-06 00:00:00.000
7 2010-06-30 00:00:00.000 2 02010, 2011-12-06 00:00:00.000
7 2010-06-30 00:00:00.000 3 10010, 2016-11-01 14:48:08.410
7 2010-06-30 00:00:00.000 4 04441, 2016-11-01 14:48:08.410
7 2011-07-05 00:00:00.000 1 09010, 2011-09-26 00:00:00.000
7 2011-07-05 00:00:00.000 1 09010, 2011-12-06 00:00:00.000
7 2011-09-26 00:00:00.000 1 10010, 2011-12-06 00:00:00.000
7 2011-12-06 00:00:00.000 1 09010, 2016-11-01 14:48:08.410
7 2011-12-06 00:00:00.000 2 04441, 2016-11-01 14:48:08.410
10 2010-04-28 00:00:00.000 1 02310, 2012-01-23 00:00:00.000
10 2010-04-28 00:00:00.000 1 02310, 2010-10-25 00:00:00.000
10 2010-04-28 00:00:00.000 1 02310, 2011-02-07 00:00:00.000
10 2010-04-28 00:00:00.000 1 02310, 2011-10-12 00:00:00.000
10 2010-10-25 00:00:00.000 1 02310, 2012-01-23 00:00:00.000
Convert SQL1 to linq
var joinTable=from a in TMP1
join a2 in TMP1
on a.ID equals a2.ID intog
from a2ing.DefaultIfEmpty()
where a.TOUROKU_NO==a2.TOUROKU_NO&a.START_DATE<a2.START_DATE
select new {
a.ID,
a. START_DATE,
a. TOUROKU_NO,
a.CD,
END_DATE=(DBNull.Value.Equals(a2.START_DATE)?(DateTime)a2.SERVERDATE:(DateTime)a2.START_DATE)
};
foreach(var item in joinTable) {
ID = item.ID.ToString();
START_DATE = item.START_DATE;
TOUROKU_NO=item.TOUROKU_NO;
CD=item.CD.ToString();
END_DATE = item.END_DATE;
}
ID STARTDATE TOROKU_NO CD END_DATE
7 2010/06/30 0:00:00 1 03010, 2011/07/05 0:00:00
7 2010/06/30 0:00:00 1 03010, 2011/09/26 0:00:00
7 2010/06/30 0:00:00 1 03010, 2011/12/06 0:00:00
7 2010/06/30 0:00:00 2 02010, 2011/12/06 0:00:00
7 2011/07/05 0:00:00 1 09010, 2011/09/26 0:00:00
7 2011/07/05 0:00:00 1 09010, 2011/12/06 0:00:00
7 2011/09/26 0:00:00 1 10010, 2011/12/06 0:00:00
10 2010/04/28 0:00:00 1 02310, 2010/10/25 0:00:00
10 2010/04/28 0:00:00 1 02310, 2011/02/07 0:00:00
10 2010/04/28 0:00:00 1 02310, 2011/10/12 0:00:00
10 2010/04/28 0:00:00 1 02310, 2012/01/23 0:00:00
10 2010/10/25 0:00:00 1 02310, 2011/02/07 0:00:00
10 2010/10/25 0:00:00 1 02310, 2011/10/12 0:00:00
10 2010/10/25 0:00:00 1 02310, 2012/01/23 0:00:00
10 2011/02/07 0:00:00 1 02310, 2011/10/12 0:00:00
10 2011/02/07 0:00:00 1 02310, 2012/01/23 0:00:00
I would like to self-join with leftjoin, but it didn't work like the SQL results above.
How do I write linq to get the same value as SQL1?
SQL2
SELECT
*,
ROW_NUMBER() OVER (Partition BY BB3. [id], BB3. [START_DATE], BB3. [TOUROKU_NO] ORDER BY BB3. [END_DATE] AS [RN]
FROM(
SELECT
BB1.*,
CASE WHEN BB2. [START_DATE] is NULL THE GETDATE() ELSE BB2. [START_DATE] ENDAS [END_DATE]
FROM TMP1 AS BB1
LEFT JOIN TMP1 AS BB2 ON (BB1.[id] = BB2.[id] AND BB1. [TOUROKU_NO] = BB2. [TOUROKU_NO] AND BB1. [START_DATE] <BB2. [START_DATE])
AS BB3
ID STARTDATE TOROKU_NO CD END_DATERN
7 2010-06-30 00:00:00.000 1 03008, 2011-07-05 00:00:00.000 1
7 2010-06-30 00:00:00.000 1 03009, 2011-09-26 00:00:00.000 2
7 2010-06-30 00:00:00.000 1 03009, 2011-12-06 00:00:00.000 3
7 2010-06-30 00:00:00.000 2 02009, 2011-12-06 00:00:00.000 1
7 2010-06-30 00:00:00.000 3 10009, 2016-11-01 14:50:38.060 1
7 2010-06-30 00:00:00.000 4 04440, 2016-11-01 14:50:38.060 1
7 2011-07-05 00:00:00.000 1 09010, 2011-09-26 00:00:00.000 1
7 2011-07-05 00:00:00.000 1 09010, 2011-12-06 00:00:00.000 2
7 2011-09-26 00:00:00.000 1 10010, 2011-12-06 00:00:00.000 1
7 2011-12-06 00:00:00.000 1 09010, 2016-11-01 14:50:38.060 1
7 2011-12-06 00:00:00.000 2 04441, 2016-11-01 14:50:38.060 1
10 2010-04-28 00:00:00.000 1 02310, 2010-10-25 00:00:00.000 1
10 2010-04-28 00:00:00.000 1 02310, 2011-02-07 00:00:00.000 2
10 2010-04-28 00:00:00.000 1 02310, 2011-10-12 00:00:00.000 3
10 2010-04-28 00:00:00.000 1 02310, 2012-01-23 00:00:00.000 4
10 2010-10-25 00:00:00.000 1 02310, 2011-02-07 00:00:00.000 1
10 2010-10-25 00:00:00.000 1 02310, 2011-10-12 00:00:00.000 2
10 2010-10-25 00:00:00.000 1 02310, 2012-01-23 00:00:00.000 3
10 2011-02-07 00:00:00.000 1 02310, 2011-10-12 00:00:00.000 1
10 2011-02-07 00:00:00.000 1 02310, 2012-01-23 00:00:00.000 2
10 2011-02-07 00:00:00.000 2 02310,03020,18010, 2016-11-01 14:50:38.060 1
10 2011-02-07 00:00:00.000 3 20010,02030, 2016-11-01 14:50:38.060 1
10 2011-02-07 00:00:00.000 4 01210, 2016-11-01 14:50:38.060 1
10 2011-02-07 00:00:00.000 5 02010, 2016-11-01 14:50:38.060 1
10 2011-02-07 00:00:00.000 6 20010, 2016-11-01 14:50:38.060 1
10 2011-02-07 00:00:00.000 7 02030, 2016-11-01 14:50:38.060 1
10 2011-10-12 00:00:00.000 1 02310, 2012-01-23 00:00:00.000 1
10 2012-01-23 00:00:00.000 1 02310, 2016-11-01 14:50:38.060 1
11 2010-04-28 00:00:00.000 1 02310, 2010-10-25 00:00:00.000 1
11 2010-04-28 00:00:00.000 1 02310, 2011-02-07 00:00:00.000 2
11 2010-04-28 00:00:00.000 1 02310, 2011-06-27 00:00:00.000 3
11 2010-04-28 00:00:00.000 1 02310, 2011-10-12 00:00:00.000 4
11 2010-04-28 00:00:00.000 1 02310, 2012-01-23 00:00:00.000 5
11 2010-10-25 00:00:00.000 1 02310, 2011-02-07 00:00:00.000 1
11 2010-10-25 00:00:00.000 1 02310, 2011-06-27 00:00:00.000 2
SQL3
SELECT
BB4.[id],
BB4. [START_DATE],
BB4. [TOUROKU_NO],
MAX (CASE WHEN BB4. [RN] = 1 THEEN BB4. [CD] ELSE' 'END) AS [CD],
MAX (CASE WHEN BB4. [RN] = 1 THEEN BB4. [END_DATE] ELSE' 'END) AS [END_DATE]
From(
SELECT
*,
ROW_NUMBER() OVER (Partition BY BB3. [id], BB3. [START_DATE], BB3. [TOUROKU_NO] ORDER BY BB3. [END_DATE] AS [RN]
FROM(
SELECT
BB1.*,
CASE WHEN BB2. [START_DATE] is NULL THE GETDATE() ELSE BB2. [START_DATE] ENDAS [END_DATE]
FROM TMP1 AS BB1
LEFT JOIN TMP1 AS BB2 ON (BB1.[id] = BB2.[id] AND BB1. [TOUROKU_NO] = BB2. [TOUROKU_NO] AND BB1. [START_DATE] <BB2. [START_DATE])
AS BB3
AS BB4
GROUP BY BB4. [id], BB4. [START_DATE], BB4. [TOUROKU_NO]
This is the final data I want to get.
ID STARTDATE TOROKU_NO CD END_DATE
7 2010-06-30 00:00:00.000 1 03010, 2011-07-05 00:00:00.000
7 2010-06-30 00:00:00.000 2 02010, 2011-12-06 00:00:00.000
7 2010-06-30 00:00:00.000 3 10010, 2016-11-01 14:53:38.593
7 2010-06-30 00:00:00.000 4 04441, 2016-11-01 14:53:38.593
7 2011-07-05 00:00:00.000 1 09010, 2011-09-26 00:00:00.000
7 2011-09-26 00:00:00.000 1 10010, 2011-12-06 00:00:00.000
7 2011-12-06 00:00:00.000 1 09010, 2016-11-01 14:53:38.593
7 2011-12-06 00:00:00.000 2 04441, 2016-11-01 14:53:38.593
10 2010-04-28 00:00:00.000 1 02310, 2010-10-25 00:00:00.000
10 2010-10-25 00:00:00.000 1 02310, 2011-02-07 00:00:00.000
10 2011-02-07 00:00:00.000 1 02310, 2011-10-12 00:00:00.000
10 2011-02-07 00:00:00.000 2 02310,03020,18010, 2016-11-01 14:53:38.593
10 2011-02-07 00:00:00.000 3 20010,02030, 2016-11-01 14:53:38.593
10 2011-02-07 00:00:00.000 4 01210, 2016-11-01 14:53:38.593
10 2011-02-07 00:00:00.000 5 02010, 2016-11-01 14:53:38.593
10 2011-02-07 00:00:00.000 6 20010, 2016-11-01 14:53:38.593
10 2011-02-07 00:00:00.000 7 02030, 2016-11-01 14:53:38.593
10 2011-10-12 00:00:00.000 1 02310, 2012-01-23 00:00:00.000
10 2012-01-23 00:00:00.000 1 02310, 2016-11-01 14:53:38.593
11 2010-04-28 00:00:00.000 1 02310, 2010-10-25 00:00:00.000
11 2010-10-25 00:00:00.000 1 02310, 2011-02-07 00:00:00.000
11 2011-02-07 00:00:00.000 1 02310, 2011-06-27 00:00:00.000
11 2011-06-27 00:00:00.000 1 02310, 2011-10-12 00:00:00.000
11 2011-10-12 00:00:00.000 1 02310, 2012-01-23 00:00:00.000
I look forward to your kind cooperation.
c# sql linq
ROW_NUMBER()The analysis function is basically not supported by EntityFramework, but this time the partition matches the grouping target.
foreach(var end_date in item.END_DATE) {
If you add the Enumerable.Select extension method of LINQ to Object to the
item.END_DATE.Select((d,i)=>new{RN=i+1,end_date=d})
You can obtain the line number as shown in .Or simply
varrn=0;
foreach(var end_date in item.END_DATE){
rn++;
and variables are also available.
I didn't scrutinize SQL, but basically there should be no problem.
I don't know the specifications, but maybe
foreach(varid in item.ID) {
The item.ID in is a grouping key, so it is all equivalent and unnecessary.
As for SQL in the questionnaire, I think it would be good if we could extract the record with the smallest END_DATE in each group, so I think we can achieve the goal with LINQ as follows.
//Grouping
var groupTable = from a in joinTable
// Sort by (group key +) END_DATE
orderby a.ID, a.START_DATE, a.TOUROKU_NO, a.END_DATE
group by new {a.ID, a.START_DATE, a.TOUROKU_NO} intog
select g.FirstOrDefault();
joinTable is a sub-question
var joinTable=from origin in TMP1
select new
{
origin.ID,
origin.START_DATE,
origin.TOUROKU_NO,
origin.CD,
END_DATE=TMP1.Where(alias=>orig.ID==alias.ID
&orig.TOUROKU_NO==alias.TOUROKU_NO
&orig.START_DATE<alias.START_DATE)
.Min(_=>(DateTime?)_.START_DATE)
?? DateTime.Now
};
It can be written as shown in .In the above two queries, ???DateTime.Now and selectg.FirstOrDefault() of END_DATE are the bottlenecks, so
END_DATE==START_DATE decision later.a of groupa a special type, and eliminate selectg.FirstOrDefault() including MIN(END_DATE).I think it will be faster if you take policies such as .
© 2024 OneMinuteCode. All rights reserved.