teratail
回答率
85.37%
teratail 10th-anniversary
teratail 10th-anniversary
質問するログイン新規登録
トップDataSetに関する質問DataTableを片側外部結合したい（Left Joinのような）

編集履歴

回答編集履歴

2

【追記２】追加

2017/11/24 08:58

投稿

退会済みユーザー
test CHANGED
@@ -16,7 +16,7 @@
16
16
 
17
17
 
18
18
 
19
- 【追記】
19
+ **【追記】**
20
20
 
21
21
 
22
22
 
@@ -177,3 +177,343 @@
177
177
  </html>
178
178
 
179
179
  ```
180
+
181
+
182
+
183
+ **【追記２】**
184
+
185
+
186
+
187
+ VB.NET に直したサンプルを下にアップしておきます。変換サービスで変換して動くように手直ししただけです。なので、VB.NET の書き方としてはアレかもしれませんが、期待通りの結果になることは確認済みです。
188
+
189
+
190
+
191
+
192
+
193
+ ```
194
+
195
+ <%@ Page Language="VB" %>
196
+
197
+
198
+
199
+ <!DOCTYPE html>
200
+
201
+
202
+
203
+ <script runat="server">
204
+
205
+
206
+
207
+ Public Class Employee
208
+
209
+ Public Property Id() As String
210
+
211
+ Get
212
+
213
+ Return m_Id
214
+
215
+ End Get
216
+
217
+ Set
218
+
219
+ m_Id = Value
220
+
221
+ End Set
222
+
223
+ End Property
224
+
225
+ Private m_Id As String
226
+
227
+ Public Property Dept() As String
228
+
229
+ Get
230
+
231
+ Return m_Dept
232
+
233
+ End Get
234
+
235
+ Set
236
+
237
+ m_Dept = Value
238
+
239
+ End Set
240
+
241
+ End Property
242
+
243
+ Private m_Dept As String
244
+
245
+ Public Property Name() As String
246
+
247
+ Get
248
+
249
+ Return m_Name
250
+
251
+ End Get
252
+
253
+ Set
254
+
255
+ m_Name = Value
256
+
257
+ End Set
258
+
259
+ End Property
260
+
261
+ Private m_Name As String
262
+
263
+ End Class
264
+
265
+
266
+
267
+ Public Class Role
268
+
269
+ Public Property Id() As String
270
+
271
+ Get
272
+
273
+ Return m_Id
274
+
275
+ End Get
276
+
277
+ Set
278
+
279
+ m_Id = Value
280
+
281
+ End Set
282
+
283
+ End Property
284
+
285
+ Private m_Id As String
286
+
287
+ Public Property Right() As String
288
+
289
+ Get
290
+
291
+ Return m_Right
292
+
293
+ End Get
294
+
295
+ Set
296
+
297
+ m_Right = Value
298
+
299
+ End Set
300
+
301
+ End Property
302
+
303
+ Private m_Right As String
304
+
305
+ End Class
306
+
307
+
308
+
309
+ Public Class JoinedTable
310
+
311
+ Public Property Id() As String
312
+
313
+ Get
314
+
315
+ Return m_Id
316
+
317
+ End Get
318
+
319
+ Set
320
+
321
+ m_Id = Value
322
+
323
+ End Set
324
+
325
+ End Property
326
+
327
+ Private m_Id As String
328
+
329
+ Public Property Dept() As String
330
+
331
+ Get
332
+
333
+ Return m_Dept
334
+
335
+ End Get
336
+
337
+ Set
338
+
339
+ m_Dept = Value
340
+
341
+ End Set
342
+
343
+ End Property
344
+
345
+ Private m_Dept As String
346
+
347
+ Public Property Name() As String
348
+
349
+ Get
350
+
351
+ Return m_Name
352
+
353
+ End Get
354
+
355
+ Set
356
+
357
+ m_Name = Value
358
+
359
+ End Set
360
+
361
+ End Property
362
+
363
+ Private m_Name As String
364
+
365
+ Public Property Right() As String
366
+
367
+ Get
368
+
369
+ Return m_Right
370
+
371
+ End Get
372
+
373
+ Set
374
+
375
+ m_Right = Value
376
+
377
+ End Set
378
+
379
+ End Property
380
+
381
+ Private m_Right As String
382
+
383
+ End Class
384
+
385
+
386
+
387
+
388
+
389
+ Protected Sub Page_Load(sender As Object, erg As EventArgs)
390
+
391
+ If Not IsPostBack Then
392
+
393
+ Dim employees As New List(Of Employee)() From {
394
+
395
+ New Employee() With {
396
+
397
+ .Id = "ID0001",
398
+
399
+ .Dept = "部署A",
400
+
401
+ .Name = "鈴木太郎"
402
+
403
+ },
404
+
405
+ New Employee() With {
406
+
407
+ .Id = "ID0002",
408
+
409
+ .Dept = "部署A",
410
+
411
+ .Name = "山田一郎"
412
+
413
+ },
414
+
415
+ New Employee() With {
416
+
417
+ .Id = "ID0003",
418
+
419
+ .Dept = "部署B",
420
+
421
+ .Name = "佐藤花子"
422
+
423
+ }
424
+
425
+ }
426
+
427
+
428
+
429
+ Dim roles As New List(Of Role)() From {
430
+
431
+ New Role() With {
432
+
433
+ .Id = "ID0001",
434
+
435
+ .Right = "〇"
436
+
437
+ },
438
+
439
+ New Role() With {
440
+
441
+ .Id = "ID0003",
442
+
443
+ .Right = "×"
444
+
445
+ },
446
+
447
+ New Role() With {
448
+
449
+ .Id = "ID0004",
450
+
451
+ .Right = "〇"
452
+
453
+ }
454
+
455
+ }
456
+
457
+
458
+
459
+ Dim query = From e In employees
460
+
461
+ Group Join r In roles
462
+
463
+ On e.Id Equals r.Id Into Group
464
+
465
+ From subrole In Group.DefaultIfEmpty()
466
+
467
+ Select New JoinedTable() With {
468
+
469
+ .Id = e.Id,
470
+
471
+ .Dept = e.Dept,
472
+
473
+ .Name = e.Name,
474
+
475
+ .Right = If(subrole Is Nothing, String.Empty, subrole.Right)
476
+
477
+ }
478
+
479
+
480
+
481
+ GridView1.DataSource = query
482
+
483
+ GridView1.DataBind()
484
+
485
+ End If
486
+
487
+ End Sub
488
+
489
+ </script>
490
+
491
+
492
+
493
+ <html xmlns="http://www.w3.org/1999/xhtml">
494
+
495
+ <head runat="server">
496
+
497
+ <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
498
+
499
+ <title></title>
500
+
501
+ </head>
502
+
503
+ <body>
504
+
505
+ <form id="form1" runat="server">
506
+
507
+ <div>
508
+
509
+ <asp:GridView ID="GridView1" runat="server"></asp:GridView>
510
+
511
+ </div>
512
+
513
+ </form>
514
+
515
+ </body>
516
+
517
+ </html>
518
+
519
+ ```

1

サンプル追記

2017/11/24 08:58

投稿

退会済みユーザー
test CHANGED
@@ -13,3 +13,167 @@
13
13
  左外部結合の実行
14
14
 
15
15
  [https://docs.microsoft.com/ja-jp/dotnet/csharp/linq/perform-left-outer-joins](https://docs.microsoft.com/ja-jp/dotnet/csharp/linq/perform-left-outer-joins)
16
+
17
+
18
+
19
+ 【追記】
20
+
21
+
22
+
23
+ 上記案でのサンプルを書いておきます。Oracle の方は Entity Framework を使えれば簡単に、CSV の方も DataSet / DataTable を作るのよりは多分少ない労力で List<T> 型のオブジェクトを作れると思います。
24
+
25
+
26
+
27
+ ```
28
+
29
+ <%@ Page Language="C#" %>
30
+
31
+
32
+
33
+ <!DOCTYPE html>
34
+
35
+
36
+
37
+ <script runat="server">
38
+
39
+
40
+
41
+ public class Employee
42
+
43
+ {
44
+
45
+ public string Id { get; set; }
46
+
47
+ public string Dept { get; set; }
48
+
49
+ public string Name { get; set; }
50
+
51
+ }
52
+
53
+
54
+
55
+ public class Role
56
+
57
+ {
58
+
59
+ public string Id { get; set; }
60
+
61
+ public string Right { get; set; }
62
+
63
+ }
64
+
65
+
66
+
67
+ public class JoinedTable
68
+
69
+ {
70
+
71
+ public string Id { get; set; }
72
+
73
+ public string Dept { get; set; }
74
+
75
+ public string Name { get; set; }
76
+
77
+ public string Right { get; set; }
78
+
79
+ }
80
+
81
+
82
+
83
+ protected void Page_Load(object sender, EventArgs erg)
84
+
85
+ {
86
+
87
+ if (!IsPostBack)
88
+
89
+ {
90
+
91
+ List<Employee> employees = new List<Employee>()
92
+
93
+ {
94
+
95
+ new Employee() { Id = "ID0001", Dept = "部署A", Name = "鈴木太郎" },
96
+
97
+ new Employee() { Id = "ID0002", Dept = "部署A", Name = "山田一郎" },
98
+
99
+ new Employee() { Id = "ID0003", Dept = "部署B", Name = "佐藤花子" }
100
+
101
+ };
102
+
103
+
104
+
105
+ List<Role> roles = new List<Role>()
106
+
107
+ {
108
+
109
+ new Role() { Id = "ID0001", Right ="〇" },
110
+
111
+ new Role() { Id = "ID0003", Right ="×" },
112
+
113
+ new Role() { Id = "ID0004", Right ="〇" }
114
+
115
+ };
116
+
117
+
118
+
119
+ var query = from e in employees
120
+
121
+ join r in roles on e.Id equals r.Id into gj
122
+
123
+ from sub in gj.DefaultIfEmpty()
124
+
125
+ select new JoinedTable
126
+
127
+ {
128
+
129
+ Id = e.Id,
130
+
131
+ Dept = e.Dept,
132
+
133
+ Name = e.Name,
134
+
135
+ Right = sub?.Right ?? string.Empty
136
+
137
+ };
138
+
139
+
140
+
141
+ GridView1.DataSource = query;
142
+
143
+ GridView1.DataBind();
144
+
145
+ }
146
+
147
+ }
148
+
149
+ </script>
150
+
151
+
152
+
153
+ <html xmlns="http://www.w3.org/1999/xhtml">
154
+
155
+ <head runat="server">
156
+
157
+ <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
158
+
159
+ <title></title>
160
+
161
+ </head>
162
+
163
+ <body>
164
+
165
+ <form id="form1" runat="server">
166
+
167
+ <div>
168
+
169
+ <asp:GridView ID="GridView1" runat="server"></asp:GridView>
170
+
171
+ </div>
172
+
173
+ </form>
174
+
175
+ </body>
176
+
177
+ </html>
178
+
179
+ ```