Datei: QueryTests/LinqTests.cs
Last Commit (fe801ed)
1 | using System; |
2 | using System.Collections.Generic; |
3 | using System.Linq; |
4 | using NUnit.Framework; |
5 | using NDO; |
6 | using NDO.Query; |
7 | using NDO.Linq; |
8 | using Reisekosten; |
9 | using Reisekosten.Personal; |
10 | using PureBusinessClasses; |
11 | using NDO.SqlPersistenceHandling; |
12 | using DataTypeTestClasses; |
13 | using System.Linq.Expressions; |
14 | |
15 | namespace QueryTests |
16 | { |
17 | ····[TestFixture] |
18 | ····public class NDOLinqTests |
19 | ····{ |
20 | ········PersistenceManager pm; |
21 | ········string mitarbeiterFields; |
22 | ········string mitarbeiterJoinFields; |
23 | ········string belegFields; |
24 | ········string pkwFahrtFields; |
25 | ········string reiseFields; |
26 | ········string reiseJoinFields; |
27 | |
28 | ········[SetUp] |
29 | ········public void SetUp() |
30 | ········{ |
31 | ············this.pm = NDOFactory.Instance.PersistenceManager; |
32 | |
33 | ············mitarbeiterFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Mitarbeiter ) ) ).SelectList; |
34 | ············mitarbeiterJoinFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Mitarbeiter ) ) ).Result( false, false, true ); |
35 | ············belegFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Beleg ) ) ).SelectList; |
36 | ············this.pkwFahrtFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( PKWFahrt ) ) ).SelectList; |
37 | ············this.reiseFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Reise ) ) ).SelectList; |
38 | ············this.reiseJoinFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Reise ) ) ).Result( false, false, true ); |
39 | ············Mitarbeiter m = new Mitarbeiter() { Vorname = "Mirko", Nachname = "Matytschak" }; |
40 | ············pm.MakePersistent( m ); |
41 | ············m = new Mitarbeiter() { Vorname = "Hans", Nachname = "Huber" }; |
42 | ············pm.MakePersistent( m ); |
43 | ············pm.Save(); |
44 | ········} |
45 | |
46 | ········[TearDown] |
47 | ········public void TearDown() |
48 | ········{ |
49 | ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm ); |
50 | ············this.pm.Delete( q.Execute() ); |
51 | ············this.pm.Save(); |
52 | ········} |
53 | |
54 | ········[Test] |
55 | ········public void CheckIfQueryWithoutWhereClauseWorks() |
56 | ········{ |
57 | ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm ); |
58 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter]", this.mitarbeiterFields ), q.GeneratedQuery ); |
59 | ········} |
60 | |
61 | ········[Test] |
62 | ········public void LinqCheckMitarbeiterQuery() |
63 | ········{ |
64 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>(); |
65 | ············string qs = vt.QueryString; |
66 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter]", qs ); |
67 | ········} |
68 | |
69 | ········[Test] |
70 | ········public void CheckIfOrderingsWork() |
71 | ········{ |
72 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>(); |
73 | ············vt.OrderBy( m => m.Vorname ).OrderByDescending( m => m.Nachname ); |
74 | ············string qs = vt.QueryString; |
75 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] ORDER BY [Mitarbeiter].[Vorname] ASC, [Mitarbeiter].[Nachname] DESC", qs ); |
76 | |
77 | ············vt = pm.Objects<Mitarbeiter>(); |
78 | ············vt.OrderBy( m => m.Vorname ).OrderByDescending( m => m.Nachname ); |
79 | ············vt.Take( 10 ).Skip( 12 ); |
80 | ············qs = vt.QueryString; |
81 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] ORDER BY [Mitarbeiter].[Vorname] ASC, [Mitarbeiter].[Nachname] DESC OFFSET 12 ROWS FETCH NEXT 10 ROWS ONLY", qs ); |
82 | ········} |
83 | |
84 | ········[Test] |
85 | ········public void LinqTestIfSimpleWhereClauseWorks() |
86 | ········{ |
87 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname == "Mirko" ); |
88 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}}", vt.QueryString ); |
89 | ············// Query for Oid values |
90 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Oid() == 5 ); |
91 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[ID] = {{0}}", vt.QueryString ); |
92 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.NDOObjectId == 5 ); |
93 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[ID] = {{0}}", vt.QueryString ); |
94 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Oid().Equals( 5 ) ); |
95 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[ID] = {{0}}", vt.QueryString ); |
96 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.NDOObjectId.Equals( 5 ) ); |
97 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[ID] = {{0}}", vt.QueryString ); |
98 | ········} |
99 | |
100 | ········[Test] |
101 | ········public void LinqCheckIfGeneratedQueryCanBeCalledTwice() |
102 | ········{ |
103 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname == "Mirko" ); |
104 | |
105 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}}", vt.QueryString ); |
106 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}}", vt.QueryString ); |
107 | ········} |
108 | |
109 | ········[Test] |
110 | ········public void LinqParameterChangesDontChangeTheQuery() |
111 | ········{ |
112 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname == "Mirko" ); |
113 | |
114 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}}", vt.QueryString ); |
115 | |
116 | ············vt.ReplaceParameters( new object[] { "Hans" } ); |
117 | |
118 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}}", vt.QueryString ); |
119 | ········} |
120 | |
121 | ········[Test] |
122 | ········public void LinqCheckIfWhereClauseWith1nRelationWorks() |
123 | ········{ |
124 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Zweck == "ADC" ); |
125 | ············string qs = vt.QueryString; |
126 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[Zweck] = {{0}}", qs ); |
127 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Oid().Equals( 5 ) ); |
128 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
129 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Oid() == 5 ); |
130 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
131 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].NDOObjectId.Equals( 5 ) ); |
132 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
133 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].NDOObjectId == 5 ); |
134 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
135 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].NDOObjectId.In(new int[] { 1, 2, 3 } ) ); |
136 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] IN (1, 2, 3)", this.mitarbeiterJoinFields ), vt.QueryString ); |
137 | ········} |
138 | |
139 | ········[Test] |
140 | ········public void LinqCheckIfWhereClauseWithAnyIn1nRelationWorks() |
141 | ········{ |
142 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen.Any(r=>r.Zweck == "ADC") ); |
143 | ············string qs = vt.QueryString; |
144 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[Zweck] = {{0}}", qs ); |
145 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen.Any( r => r.Oid().Equals( 5 ) ) ); |
146 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
147 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen.Any( r => r.Oid() == 5 ) ); |
148 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
149 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen.Any( r => r.NDOObjectId.Equals( 5 ) ) ); |
150 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
151 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen.Any( r => r.NDOObjectId == 5 ) ); |
152 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
153 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen.Any( r => r.NDOObjectId.In( new int[] { 1, 2, 3 } ) ) ); |
154 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] IN (1, 2, 3)", this.mitarbeiterJoinFields ), vt.QueryString ); |
155 | ········} |
156 | |
157 | |
158 | ········[Test] |
159 | ········public void LinqCheckIfWhereClauseWith11RelationWorks() |
160 | ········{ |
161 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse.Lkz.Like( "D%" ) ); |
162 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Adresse] ON [Adresse].[ID] = [Mitarbeiter].[IDAdresse] WHERE [Adresse].[Lkz] LIKE {{0}}", vt.QueryString ); |
163 | ········} |
164 | |
165 | ········[Test] |
166 | ········public void LinqCheckIfWhereClauseWithOidIn11RelationWorks() |
167 | ········{ |
168 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse.Oid() == 5 ); |
169 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[IDAdresse] = {{0}}", vt.QueryString ); |
170 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse.Oid().In(new[]{ 1, 2, 3 } ) ); |
171 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[IDAdresse] IN (1, 2, 3)", vt.QueryString ); |
172 | ········} |
173 | |
174 | ········[Test] |
175 | ········public void LinqCheckIfMultipleRelationsWork() |
176 | ········{ |
177 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse.Lkz.Like( "D%" ) && m.Reisen[Any.Index].Länder[Any.Index].Name == "D" ); |
178 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Adresse] ON [Adresse].[ID] = [Mitarbeiter].[IDAdresse] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] INNER JOIN [relLandReise] ON [Reise].[ID] = [relLandReise].[IDReise] INNER JOIN [Land] ON [Land].[ID] = [relLandReise].[IDLand] WHERE [Adresse].[Lkz] LIKE {{0}} AND [Land].[Name] = {{1}}", vt.QueryString ); |
179 | ········} |
180 | |
181 | ········[Test] |
182 | ········public void LinqCheckOidWithTable() |
183 | ········{ |
184 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Länder[Any.Index].Oid() == 55 ); |
185 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] INNER JOIN [relLandReise] ON [Reise].[ID] = [relLandReise].[IDReise] WHERE [relLandReise].[IDLand] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
186 | ········} |
187 | |
188 | ········[Test] |
189 | ········public void LinqCheckThatOneJoinAppearsOnlyOneTime() |
190 | ········{ |
191 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse.Lkz.Like("D%") && m.Adresse.Ort != "Bad Tölz" ); |
192 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Adresse] ON [Adresse].[ID] = [Mitarbeiter].[IDAdresse] WHERE [Adresse].[Lkz] LIKE {{0}} AND [Adresse].[Ort] <> {{1}}", vt.QueryString ); |
193 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Zweck == "ADC" || m.Reisen[Any.Index].Zweck == "ADW" ); |
194 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[Zweck] = {{0}} OR [Reise].[Zweck] = {{1}}", vt.QueryString ); |
195 | ········} |
196 | |
197 | ········[Test] |
198 | ········public void LinqCheckNotOperator() |
199 | ········{ |
200 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where(m => !(m.Nachname == "Matytschak") ); |
201 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE NOT ([Mitarbeiter].[Nachname] = {{0}})", vt.QueryString ); |
202 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.Like( "M%" ) && !(m.Nachname == "Matytschak") ); |
203 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] LIKE {{0}} AND NOT ([Mitarbeiter].[Nachname] = {{1}})", vt.QueryString ); |
204 | ············vt = pm.Objects<Mitarbeiter>().Where( m => !(m.Vorname.Like( "M%" ) && m.Nachname == "Matytschak") ); |
205 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE NOT ([Mitarbeiter].[Vorname] LIKE {{0}} AND [Mitarbeiter].[Nachname] = {{1}})", vt.QueryString ); |
206 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Zweck == "ADC" || !(m.Reisen[Any.Index].Zweck == "ADW") ); |
207 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[Zweck] = {{0}} OR NOT ([Reise].[Zweck] = {{1}})", vt.QueryString ); |
208 | ············vt = pm.Objects<Mitarbeiter>().Where( m => !(m.Reisen[Any.Index].Zweck == "ADC" || m.Reisen[Any.Index].Zweck == "ADW") ); |
209 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE NOT ([Reise].[Zweck] = {{0}} OR [Reise].[Zweck] = {{1}})", vt.QueryString ); |
210 | ············vt = pm.Objects<Mitarbeiter>().Where( m => !(m.Reisen[Any.Index].Länder[Any.Index].IsInEu == true) ); |
211 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] INNER JOIN [relLandReise] ON [Reise].[ID] = [relLandReise].[IDReise] INNER JOIN [Land] ON [Land].[ID] = [relLandReise].[IDLand] WHERE NOT ([Land].[IsInEu] = {{0}})", vt.QueryString ); |
212 | ········} |
213 | |
214 | ········[Test] |
215 | ········public void LinqCheckBetween() |
216 | ········{ |
217 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.Between( "A", "B" ) ); |
218 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] BETWEEN {{0}} AND {{1}}", vt.QueryString ); |
219 | ············vt = pm.Objects<Mitarbeiter>().Where( m => !m.Vorname.Between( "A", "B" ) ); |
220 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE NOT [Mitarbeiter].[Vorname] BETWEEN {{0}} AND {{1}}", vt.QueryString ); |
221 | ········} |
222 | |
223 | ········[Test] |
224 | ········public void LinqTestValueType() |
225 | ········{ |
226 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Position.X > 2 && m.Position.Y < 5); |
227 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Position_X] > {{0}} AND [Mitarbeiter].[Position_Y] < {{1}}", vt.QueryString ); |
228 | ········} |
229 | |
230 | ········[Test] |
231 | ········public void TestValueTypeRelation() |
232 | ········{ |
233 | ············var vt = pm.Objects<Sozialversicherungsnummer>().Where( s => s.Angestellter.Position.X > 2 && s.Angestellter.Position.Y < 5 ); |
234 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Sozialversicherungsnummer ) ) ).Result( false, false, true ); |
235 | ············Assert.AreEqual( $"SELECT {fields} FROM [Sozialversicherungsnummer] INNER JOIN [Mitarbeiter] ON [Mitarbeiter].[ID] = [Sozialversicherungsnummer].[IDSozial] WHERE [Mitarbeiter].[Position_X] > {{0}} AND [Mitarbeiter].[Position_Y] < {{1}}", vt.QueryString ); |
236 | ········} |
237 | |
238 | ········[Test] |
239 | ········public void LinqCheckFetchGroupInitializationWithExpressions() |
240 | ········{ |
241 | ············//TODO: Clarify how this should be implemented in Linq |
242 | ············//FetchGroup<Mitarbeiter> fg = new FetchGroup<Mitarbeiter>( m => m.Vorname, m => m.Nachname ); |
243 | ············//Assert.AreEqual( fg.Count, 2, "Count should be 2" ); |
244 | ············//Assert.AreEqual( "Vorname", fg[0], "Name wrong #1" ); |
245 | ············//Assert.AreEqual( "Nachname", fg[1], "Name wrong #2" ); |
246 | ········} |
247 | |
248 | ········[Test] |
249 | ········public void LinqCheckIfMultiKeysWork() |
250 | ········{ |
251 | ············var orderDetail = new OrderDetail(); |
252 | ············var vt = pm.Objects<OrderDetail>().Where( od => od.Oid() == orderDetail.NDOObjectId ); |
253 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( OrderDetail ) ) ).SelectList; |
254 | ············Assert.AreEqual( $"SELECT {fields} FROM [OrderDetail] WHERE [OrderDetail].[IDProduct] = {{0}} AND [OrderDetail].[IDOrder] = {{1}}", vt.QueryString ); |
255 | #if ignored |
256 | ············bool thrown = false; |
257 | ············try |
258 | ············{ |
259 | ················vt = pm.Objects<OrderDetail>().Where( od => od.Oid() == -4 ); |
260 | ················string s = vt.QueryString; |
261 | ············} |
262 | ············catch (Exception) |
263 | ············{ |
264 | ················thrown = true; |
265 | ············} |
266 | ············// This fails, because the parameter won't be checked by the parser. |
267 | ············// It isn't checked in the WherePart-Generator neither because it checks only, if the right side of the comparism is a parameter. |
268 | ············// We need to check the oid mapping to detect this situation. |
269 | ············// Or we might let it be, because we will get an exception anyway, if the query is executed. |
270 | ············Assert.AreEqual( true, thrown ); |
271 | #endif |
272 | ········} |
273 | |
274 | ········[Test] |
275 | ········public void LinqTestBooleanExpression() |
276 | ········{ |
277 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Land ) ) ).SelectList; |
278 | ············var vt = pm.Objects<Land>().Where( l => l.IsInEu == true ); |
279 | ············Assert.AreEqual( $"SELECT {fields} FROM [Land] WHERE [Land].[IsInEu] = {{0}}", vt.QueryString ); |
280 | |
281 | ············vt = pm.Objects<Land>().Where( l => l.IsInEu ); |
282 | ············Assert.AreEqual( $"SELECT {fields} FROM [Land] WHERE [Land].[IsInEu] = 1", vt.QueryString ); |
283 | |
284 | ············vt = pm.Objects<Land>().Where( l => l.IsInEu && l.Name == "Lala" ); |
285 | ············Assert.AreEqual( $"SELECT {fields} FROM [Land] WHERE [Land].[IsInEu] = 1 AND [Land].[Name] = {{0}}", vt.QueryString ); |
286 | |
287 | ············vt = pm.Objects<Land>().Where( l => l.Name == "Lala" && l.IsInEu ); |
288 | ············Assert.AreEqual( $"SELECT {fields} FROM [Land] WHERE [Land].[IsInEu] = 1 AND [Land].[Name] = {{0}}", vt.QueryString ); |
289 | ········} |
290 | |
291 | ········[Test] |
292 | ········public void LinqTestSuperclasses() |
293 | ········{ |
294 | ············var vt = pm.Objects<Kostenpunkt>(); |
295 | ············Assert.AreEqual( $"SELECT {this.belegFields} FROM [Beleg];\r\nSELECT {this.pkwFahrtFields} FROM [PKWFahrt]", vt.QueryString ); |
296 | ········} |
297 | |
298 | ········[Test] |
299 | ········public void CanAddPrefetches() |
300 | ········{ |
301 | ············var vt = pm.Objects<Mitarbeiter>(); |
302 | ············vt.AddPrefetch( m => m.Reisen ); |
303 | ············vt.AddPrefetch( m => m.Reisen[Any.Index].Länder ); |
304 | ············var list = vt.Prefetches.ToList(); |
305 | ············Assert.AreEqual( 2, list.Count ); |
306 | ············Assert.AreEqual( "Reisen", list[0] ); |
307 | ············Assert.AreEqual( "Reisen.Länder", list[1] ); |
308 | ········} |
309 | |
310 | ········[Test] |
311 | ········public void LinqSimplePrefetchWorks() |
312 | ········{ |
313 | ············var vt = pm.Objects<Mitarbeiter>(); |
314 | ············vt.AddPrefetch( m => m.Reisen ); |
315 | ············var s = vt.QueryString; |
316 | ········} |
317 | |
318 | ········[Test, Ignore("This is not implemented")] |
319 | ········public void LinqPrefetchWithBidirectionalRelationWorks() |
320 | ········{ |
321 | ············Assert.That( false, "Not implemented" ); |
322 | ············// With Bidirectional Relation (vorhandener JOIN) |
323 | ········} |
324 | |
325 | ········[Test, Ignore( "This is not implemented" )] |
326 | ········public void LinqPrefetchWithMonoRelationWorks() |
327 | ········{ |
328 | ············Assert.That( false, "Not implemented" ); |
329 | ············// Monodirektional (neuer JOIN) |
330 | ········} |
331 | |
332 | ········[Test, Ignore( "This is not implemented" )] |
333 | ········public void LinqPrefetchWithDifferentRelationRolesWorks() |
334 | ········{ |
335 | ············Assert.That( false, "Not implemented" ); |
336 | ············// Unterschiedliche Relationen werden auseinandergehalten |
337 | ········} |
338 | |
339 | ········[Test] |
340 | ········public void LinqTestPolymorphicRelationQueries() |
341 | ········{ |
342 | ············// We have to patch the AccessorName here, since the Enhancer doesn't create the AccessorName automatically. |
343 | ············// This will change as soon as we update the tests. |
344 | ············pm.NDOMapping.FindClass( typeof( Kostenpunkt ) ).FindField( "datum" ).AccessorName = "Datum"; |
345 | ············pm.NDOMapping.FindClass( typeof( Beleg ) ).FindField( "datum" ).AccessorName = "Datum"; |
346 | ············pm.NDOMapping.FindClass( typeof( PKWFahrt ) ).FindField( "datum" ).AccessorName = "Datum"; |
347 | |
348 | ············var vt = pm.Objects<Reise>().Where(r => r.Kostenpunkte[Any.Index].Datum == DateTime.Now.Date); |
349 | |
350 | ············Assert.AreEqual( $"SELECT {reiseJoinFields} FROM [Reise] INNER JOIN [relBelegKostenpunkt] ON [Reise].[ID] = [relBelegKostenpunkt].[IDReise] INNER JOIN [Beleg] ON [Beleg].[ID] = [relBelegKostenpunkt].[IDBeleg] AND [relBelegKostenpunkt].[TCBeleg] = 926149172 WHERE [Beleg].[Datum] = {{0}} UNION \r\nSELECT {reiseJoinFields} FROM [Reise] INNER JOIN [relBelegKostenpunkt] ON [Reise].[ID] = [relBelegKostenpunkt].[IDReise] INNER JOIN [PKWFahrt] ON [PKWFahrt].[ID] = [relBelegKostenpunkt].[IDBeleg] AND [relBelegKostenpunkt].[TCBeleg] = 734406058 WHERE [PKWFahrt].[Datum] = {{0}}", vt.QueryString ); |
351 | ········} |
352 | |
353 | ········[Test] |
354 | ········public void LinqTest1To1() |
355 | ········{ |
356 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.Zimmer.Zimmer == "abc"); |
357 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Buero] ON [Buero].[ID] = [Mitarbeiter].[IDBuero] WHERE [Buero].[Zimmer] = {{0}}", vt.QueryString ); |
358 | ········} |
359 | |
360 | |
361 | ········[Test] |
362 | ········public void LinqTest1To1Bidirectional() |
363 | ········{ |
364 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.SVN.SVN == 4711); |
365 | ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "sn.nummer = 'abc'" ); |
366 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Sozialversicherungsnummer] ON [Sozialversicherungsnummer].[ID] = [Mitarbeiter].[IDSozial] WHERE [Sozialversicherungsnummer].[Nummer] = 'abc'", this.mitarbeiterJoinFields ), q.GeneratedQuery ); |
367 | ············var vt2 = pm.Objects<Sozialversicherungsnummer>().Where(s=>s.Angestellter.Vorname == "Mirko"); |
368 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Sozialversicherungsnummer ) ) ).Result( false, false, true ); |
369 | ············Assert.AreEqual( $"SELECT {fields} FROM [Sozialversicherungsnummer] INNER JOIN [Mitarbeiter] ON [Mitarbeiter].[ID] = [Sozialversicherungsnummer].[IDSozial] WHERE [Mitarbeiter].[Vorname] = {{0}}", vt2.QueryString ); |
370 | ········} |
371 | |
372 | ········[Test] |
373 | ········public void LinqTest1To1BiWithTable() |
374 | ········{ |
375 | ············var vt1 = pm.Objects<Zertifikat>().Where(z=>z.SGN.Key == "abc"); |
376 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Zertifikat ) ) ).Result( false, false, true ); |
377 | ············Assert.AreEqual( $"SELECT {fields} FROM [Zertifikat] INNER JOIN [relSignaturZertifikat] ON [Zertifikat].[ID] = [relSignaturZertifikat].[IDZertifikat] INNER JOIN [Signatur] ON [Signatur].[ID] = [relSignaturZertifikat].[IDSignatur] WHERE [Signatur].[Signature] = {{0}}", vt1.QueryString ); |
378 | ············var vt2 = pm.Objects<Signatur>().Where(sg=>sg.Owner.Key == -4); |
379 | ············fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Signatur ) ) ).Result( false, false, true ); |
380 | ············Assert.AreEqual( $"SELECT {fields} FROM [Signatur] INNER JOIN [relSignaturZertifikat] ON [Signatur].[ID] = [relSignaturZertifikat].[IDSignatur] INNER JOIN [Zertifikat] ON [Zertifikat].[ID] = [relSignaturZertifikat].[IDZertifikat] WHERE [Zertifikat].[Schlüssel] = {{0}}", vt2.QueryString); |
381 | ········} |
382 | |
383 | ········[Test] |
384 | ········public void LinqTest1ToNWithTable() |
385 | ········{ |
386 | ············// We have to patch the AccessorName here, since the Enhancer doesn't create the AccessorName automatically. |
387 | ············// This will change as soon as we update the tests. |
388 | ············pm.NDOMapping.FindClass( typeof( Mitarbeiter ) ).FindRelation( "reiseBüros" ).AccessorName = "ReiseBüros"; |
389 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.ReiseBüros.ElementAt(Any.Index).Name == "abc"); |
390 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [relMitarbeiterReisebuero] ON [Mitarbeiter].[ID] = [relMitarbeiterReisebuero].[IDMitarbeiter] INNER JOIN [Reisebuero] ON [Reisebuero].[ID] = [relMitarbeiterReisebuero].[IDReisebuero] WHERE [Reisebuero].[Name] = {{0}}", vt.QueryString ); |
391 | ········} |
392 | |
393 | ········[Test] |
394 | ········public void LinqTestIfQueryForNonNullOidsWorks() |
395 | ········{ |
396 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Länder.Oid() != null ); |
397 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] INNER JOIN [relLandReise] ON [Reise].[ID] = [relLandReise].[IDReise] WHERE [relLandReise].[IDLand] IS NOT NULL", vt.QueryString ); |
398 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Länder[Any.Index].NDOObjectId != null ); |
399 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] INNER JOIN [relLandReise] ON [Reise].[ID] = [relLandReise].[IDReise] WHERE [relLandReise].[IDLand] IS NOT NULL", vt.QueryString ); |
400 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse.NDOObjectId != null ); |
401 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[IDAdresse] IS NOT NULL", vt.QueryString ); |
402 | ········} |
403 | |
404 | ········[Test] |
405 | ········public void LinqTestIfQueryWithNonNullRelationWorks() |
406 | ········{ |
407 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse != null ); |
408 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[IDAdresse] IS NOT NULL", vt.QueryString ); |
409 | ········} |
410 | |
411 | ········[Test] |
412 | ········public void LinqTestIfQueryWithNullRelationWorks() |
413 | ········{ |
414 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse == null ); |
415 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[IDAdresse] IS NULL", vt.QueryString ); |
416 | ········} |
417 | |
418 | ········[Test] |
419 | ········public void LinqTestIfIsNullWithStringWorks() |
420 | ········{ |
421 | ············var sql = $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IS NULL"; |
422 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname == null ); |
423 | ············Assert.AreEqual( sql, vt.QueryString ); |
424 | ············vt = pm.Objects<Mitarbeiter>().Where( m => null == m.Vorname ); |
425 | ············Assert.AreEqual( sql, vt.QueryString ); |
426 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.Equals(null) ); |
427 | ············Assert.AreEqual( sql, vt.QueryString ); |
428 | ········} |
429 | |
430 | ········[Test] |
431 | ········public void CanCombineExpressions() |
432 | ········{ |
433 | ············var sql = $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}} AND [Mitarbeiter].[Nachname] = {{1}}"; |
434 | ············Expression<Func<Mitarbeiter,bool>> expr1 = m=>m.Vorname == "Mirko"; |
435 | ············Expression<Func<Mitarbeiter,bool>> expr2 = m=>m.Nachname == "Matytschak"; |
436 | ············var combined = expr1.Combine(expr2, System.Linq.Expressions.ExpressionType.And); |
437 | ············var vt = pm.Objects<Mitarbeiter>().Where( combined ); |
438 | ············Assert.AreEqual( sql, vt.QueryString ); |
439 | ········} |
440 | |
441 | |
442 | ········[Test] |
443 | ········public void LinqTestIfIsNotNullWithStringWorks() |
444 | ········{ |
445 | ············var sql = $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IS NOT NULL"; |
446 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname != null ); |
447 | ············Assert.AreEqual( sql, vt.QueryString ); |
448 | ············vt = pm.Objects<Mitarbeiter>().Where( m => null != m.Vorname ); |
449 | ············Assert.AreEqual( sql, vt.QueryString ); |
450 | ············vt = pm.Objects<Mitarbeiter>().Where( m => !m.Vorname.Equals( null ) ); |
451 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE NOT [Mitarbeiter].[Vorname] IS NULL", vt.QueryString ); |
452 | ········} |
453 | |
454 | ········[Test] |
455 | ········public void LinqTestIfIsNullWithGuidWorks() |
456 | ········{ |
457 | ············// The query will fetch for DataContainerDerived objects, too. We need to define the Accessor on-the-fly for this class, since |
458 | ············// the accessor isn't defined in the original mapping file. |
459 | ············// We also test for "StartsWith", because the query contains additional text, which doesn't matter here. |
460 | ············pm.NDOMapping.FindClass( typeof( DataContainerDerived ) ).FindField( "guidVar" ).AccessorName = "GuidVar"; |
461 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( DataContainer ) ) ).SelectList; |
462 | ············var sql = $"SELECT {fields} FROM [DataContainer] WHERE [DataContainer].[GuidVar] IS NULL"; |
463 | ············var vt = pm.Objects<DataContainer>().Where( m => m.GuidVar == null ); |
464 | ············Assert.That( vt.QueryString.StartsWith( sql) ); |
465 | ············vt = pm.Objects<DataContainer>().Where( m => m.GuidVar == Guid.Empty ); |
466 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
467 | ············vt = pm.Objects<DataContainer>().Where( m => Guid.Empty == m.GuidVar ); |
468 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
469 | ············vt = pm.Objects<DataContainer>().Where( m => m.GuidVar.Equals(Guid.Empty) ); |
470 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
471 | ········} |
472 | |
473 | ········[Test] |
474 | ········public void LinqTestIfIsNotNullWithGuidWorks() |
475 | ········{ |
476 | ············// The query will fetch for DataContainerDerived objects, too. We need to define the Accessor on-the-fly for this class, since |
477 | ············// the accessor isn't defined in the original mapping file. |
478 | ············// We also test for "StartsWith", because the query contains additional text, which doesn't matter here. |
479 | ············pm.NDOMapping.FindClass( typeof( DataContainerDerived ) ).FindField( "guidVar" ).AccessorName = "GuidVar"; |
480 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( DataContainer ) ) ).SelectList; |
481 | ············var sql = $"SELECT {fields} FROM [DataContainer] WHERE [DataContainer].[GuidVar] IS NOT NULL"; |
482 | ············var vt = pm.Objects<DataContainer>().Where( m => m.GuidVar != null ); |
483 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
484 | ············vt = pm.Objects<DataContainer>().Where( m => m.GuidVar != Guid.Empty ); |
485 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
486 | ············vt = pm.Objects<DataContainer>().Where( m => Guid.Empty != m.GuidVar ); |
487 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
488 | ············vt = pm.Objects<DataContainer>().Where( m => !m.GuidVar.Equals( Guid.Empty ) ); |
489 | ············Assert.That( vt.QueryString.StartsWith( $"SELECT {fields} FROM [DataContainer] WHERE NOT [DataContainer].[GuidVar] IS NULL" ) ); |
490 | ········} |
491 | |
492 | ········[Test] |
493 | ········public void LinqTestIfIsNullWithDateTimeWorks() |
494 | ········{ |
495 | ············// The query will fetch for DataContainerDerived objects, too. We need to define the Accessor on-the-fly for this class, since |
496 | ············// the accessor isn't defined in the original mapping file. |
497 | ············// We also test for "StartsWith", because the query contains additional text, which doesn't matter here. |
498 | ············pm.NDOMapping.FindClass( typeof( DataContainerDerived ) ).FindField( "dateTimeVar" ).AccessorName = "DateTimeVar"; |
499 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( DataContainer ) ) ).SelectList; |
500 | ············var sql = $"SELECT {fields} FROM [DataContainer] WHERE [DataContainer].[DateTimeVar] IS NULL"; |
501 | ············var vt = pm.Objects<DataContainer>().Where( m => m.DateTimeVar == null ); |
502 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
503 | ············vt = pm.Objects<DataContainer>().Where( m => m.DateTimeVar == DateTime.MinValue ); |
504 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
505 | ············vt = pm.Objects<DataContainer>().Where( m => DateTime.MinValue == m.DateTimeVar ); |
506 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
507 | ············vt = pm.Objects<DataContainer>().Where( m => m.DateTimeVar.Equals(DateTime.MinValue) ); |
508 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
509 | ········} |
510 | |
511 | ········[Test] |
512 | ········public void LinqTestIfIsNotNullWithDateTimeWorks() |
513 | ········{ |
514 | ············// The query will fetch for DataContainerDerived objects, too. We need to define the Accessor on-the-fly for this class, since |
515 | ············// the accessor isn't defined in the original mapping file. |
516 | ············// We also test for "StartsWith", because the query contains additional text, which doesn't matter here. |
517 | ············pm.NDOMapping.FindClass( typeof( DataContainerDerived ) ).FindField( "dateTimeVar" ).AccessorName = "DateTimeVar"; |
518 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( DataContainer ) ) ).SelectList; |
519 | ············var sql = $"SELECT {fields} FROM [DataContainer] WHERE [DataContainer].[DateTimeVar] IS NOT NULL"; |
520 | ············var vt = pm.Objects<DataContainer>().Where( m => m.DateTimeVar != null ); |
521 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
522 | ············vt = pm.Objects<DataContainer>().Where( m => m.DateTimeVar != DateTime.MinValue ); |
523 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
524 | ············vt = pm.Objects<DataContainer>().Where( m => DateTime.MinValue != m.DateTimeVar ); |
525 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
526 | ············vt = pm.Objects<DataContainer>().Where( m => !m.DateTimeVar.Equals( DateTime.MinValue ) ); |
527 | ············Assert.That( vt.QueryString.StartsWith( $"SELECT {fields} FROM [DataContainer] WHERE NOT [DataContainer].[DateTimeVar] IS NULL" ) ); |
528 | ········} |
529 | |
530 | ········[Test] |
531 | ········public void LinqTestIfInClauseWorks() |
532 | ········{ |
533 | ············// Note: Single quotes must be escaped to avoid Sql injection attacks |
534 | ············var arr = new[] { "Mirko", "Hans'" }; |
535 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.Vorname.In(arr));············ |
536 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IN ('Mirko', 'Hans''')", vt.QueryString ); |
537 | ············vt = pm.Objects<Mitarbeiter>().Where(m => m.Vorname.In(new[] { "Mirko", "Ha'ns" })); |
538 | ············Assert.AreEqual($"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IN ('Mirko', 'Ha''ns')", vt.QueryString); |
539 | ········} |
540 | |
541 | ········[Test] |
542 | ········public void LinqTestIfInClauseWithNumbersWorks() |
543 | ········{ |
544 | ············// NDO won't check, if the types match |
545 | ············var arr = new[] { 1,2,3,4,5 }; |
546 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.Vorname.In(arr)); |
547 | Assert. AreEqual( $"SELECT { this. mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter]. [Vorname] IN ( 1, 2, 3, 4, 5) ", vt. QueryString) ; |
548 | ········} |
549 | |
550 | ········[Test] |
551 | ········public void LinqTestIfInClauseWithGuidsWorks() |
552 | ········{ |
553 | ············var guids = new Guid[]{ Guid.NewGuid(), Guid.NewGuid() }; |
554 | ············// NDO won't check, if the types match |
555 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.Vorname.In(guids)); |
556 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IN ('{guids[0]}', '{guids[1]}')", vt.QueryString ); |
557 | ········} |
558 | |
559 | ········[Test] |
560 | ········public void LinqTestIfRelationInInClauseWorks() |
561 | ········{ |
562 | ············var arr = new[] { 1, 2, 3, 4, 5 }; |
563 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.Reisen.Oid().In(arr)); |
564 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] IN (1, 2, 3, 4, 5)", vt.QueryString ); |
565 | ············vt = pm.Objects<Mitarbeiter>().Where(m => m.Reisen.Oid().In(new[] { 1, 2, 3, 4, 5 })); |
566 | ············Assert.AreEqual($"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] IN (1, 2, 3, 4, 5)", vt.QueryString); |
567 | ········} |
568 | |
569 | ········[Test] |
570 | ········public void TestIfOidWithInClauseWorks() |
571 | ········{ |
572 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.Oid().In(new[] { 1, 2, 3, 4, 5 })); |
573 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[ID] IN (1, 2, 3, 4, 5)", vt.QueryString ); |
574 | ········} |
575 | |
576 | ········[Test] |
577 | ········public void TestIfLinqQueryWithOidParameterWorks() |
578 | ········{ |
579 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Oid(0).Equals( 5 ) ); |
580 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
581 | ············vt = pm.Objects<Mitarbeiter>().Where( m => (int)m.Reisen[Any.Index].NDOObjectId[0] == 5 ); |
582 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
583 | ········} |
584 | |
585 | ········[Test] |
586 | ········public void TestIfLinqQueryForNonNullOidsWorks() |
587 | ········{ |
588 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Oid() != null ); |
589 | ············var qs = vt.QueryString; |
590 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] IS NOT NULL", qs ); |
591 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Länder[Any.Index].Oid() != null ); |
592 | ············qs = vt.QueryString; |
593 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] INNER JOIN [relLandReise] ON [Reise].[ID] = [relLandReise].[IDReise] WHERE [relLandReise].[IDLand] IS NOT NULL", qs ); |
594 | ········} |
595 | |
596 | ········[Test] |
597 | ········public void ComparismWithStringWorks() |
598 | ········{ |
599 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.GreaterEqual( "abc" ) && m.Vorname.LowerThan( "abcd") ); |
600 | ············var qs = vt.QueryString; |
601 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] >= {0}" ) > -1 ); |
602 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] < {1}" ) > -1 ); |
603 | ········} |
604 | |
605 | ········[Test] |
606 | ········public void ComparismWithEmptyStringWorks() |
607 | ········{ |
608 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.GreaterEqual( String.Empty ) && m.Vorname == String.Empty ); |
609 | ············var qs = vt.QueryString; |
610 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] >= {0}" ) > -1 ); |
611 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] = {0}" ) > -1 ); |
612 | ············// We compare with String.Empty two times. The ExpressionTreeTransformer recognizes the equality and |
613 | ············// creates only one parameter. |
614 | ········} |
615 | |
616 | ········[Test] |
617 | ········public void CanFetchForNotStringIsNullOrEmpty() |
618 | ········{ |
619 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname != null && m.Vorname != String.Empty ); |
620 | ············var qs = vt.QueryString; |
621 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IS NOT NULL AND [Mitarbeiter].[Vorname] <> {{0}}", vt.QueryString ); |
622 | ········} |
623 | |
624 | ········[Test] |
625 | ········public void CanUseStringsInINClauses() |
626 | ········{ |
627 | ············List<string> strings = new List<string>{ "1", "2", "3" }; |
628 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.Like("Hallo") && !m.Nachname.In( strings ) ); |
629 | ············var qs = vt.QueryString; |
630 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] LIKE {{0}} AND NOT [Mitarbeiter].[Nachname] IN ('1', '2', '3')", vt.QueryString ); |
631 | ········} |
632 | |
633 | |
634 | |
635 | ········[Test] |
636 | ········public void ComparismBetweenTwoFieldWorks() |
637 | ········{ |
638 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.GreaterEqual( m.Nachname ) ); |
639 | ············var qs = vt.QueryString; |
640 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] >= [Mitarbeiter].[Nachname]" ) > -1 ); |
641 | ········} |
642 | |
643 | ········[Test] |
644 | ········public void FlipParametersWorks() |
645 | ········{ |
646 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => "abc" == m.Vorname ); |
647 | ············var qs = vt.QueryString; |
648 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] = {0}" ) > -1 ); |
649 | ········} |
650 | |
651 | ········[Test] |
652 | ········public void FlipParametersInComplexExpressionsWorks() |
653 | ········{ |
654 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => "abc" == m.Vorname && "def" == m.Nachname ); |
655 | ············var qs = vt.QueryString; |
656 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] = {0}" ) > -1 ); |
657 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Nachname] = {1}" ) > -1 ); |
658 | ········} |
659 | |
660 | ········[Test] |
661 | ········public void FlipParametersWithGTWorks() |
662 | ········{ |
663 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => "abc".GreaterThan(m.Vorname) ); |
664 | ············var qs = vt.QueryString; |
665 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] < {0}" ) > -1 ); |
666 | ········} |
667 | |
668 | ········[Test] |
669 | ········public void FlipParametersWithGEWorks() |
670 | ········{ |
671 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => "abc".GreaterEqual(m.Vorname) ); |
672 | ············var qs = vt.QueryString; |
673 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] <= {0}" ) > -1 ); |
674 | ········} |
675 | ········[Test] |
676 | ········public void FlipParametersWithLTWorks() |
677 | ········{ |
678 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => "abc".LowerThan(m.Vorname) ); |
679 | ············var qs = vt.QueryString; |
680 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] > {0}" ) > -1 ); |
681 | ········} |
682 | ········[Test] |
683 | ········public void FlipParametersWithLEWorks() |
684 | ········{ |
685 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => "abc".LowerEqual(m.Vorname) ); |
686 | ············var qs = vt.QueryString; |
687 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] >= {0}" ) > -1 ); |
688 | ········} |
689 | |
690 | ········[Test] |
691 | ········public void GTWorks() |
692 | ········{ |
693 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.GreaterThan( "abc" ) ); |
694 | ············var qs = vt.QueryString; |
695 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] > {0}" ) > -1 ); |
696 | ········} |
697 | |
698 | ········/// <summary> |
699 | ········/// This class provides Signatures for Server Functions |
700 | ········/// </summary> |
701 | ········class ServerFunctions |
702 | ········{ |
703 | ············public static string TestFunction(string name, int val) |
704 | ············{ |
705 | ················return null; |
706 | ············} |
707 | |
708 | ············public static int ParameterlessFunction() |
709 | ············{ |
710 | ················return 0; |
711 | ············} |
712 | |
713 | ············[ServerFunction("JSON_VALUE")] |
714 | ············public static int JsonValueAsInt(string json, string path) |
715 | ············{ |
716 | ················return 0; |
717 | ············} |
718 | |
719 | ············[ServerFunction( "JSON_VALUE" )] |
720 | ············public static string JsonValueAsString( string json, string path ) |
721 | ············{ |
722 | ················return null; |
723 | ············} |
724 | |
725 | ········} |
726 | |
727 | ········[Test] |
728 | ········public void CanCallServerFunctions() |
729 | ········{ |
730 | ············var vt = pm.Objects<Reise>().Where( m => m.Zweck.GreaterThan( ServerFunctions.TestFunction( m.Zweck, 42 ) ) ); |
731 | ············var qs = vt.QueryString; |
732 | ············Assert.AreEqual( qs, $"SELECT {this.reiseFields} FROM [Reise] WHERE [Reise].[Zweck] > TestFunction([Reise].[Zweck], {{0}})" ); |
733 | ············vt = pm.Objects<Reise>().Where( m => ServerFunctions.ParameterlessFunction() > 2143 ); |
734 | ············qs = vt.QueryString; |
735 | ············Assert.AreEqual( qs, $"SELECT {this.reiseFields} FROM [Reise] WHERE ParameterlessFunction() > {{0}}" ); |
736 | ············// Assume for a moment, that Reise.Zweck contains a Json string. |
737 | ············vt = pm.Objects<Reise>().Where( m => ServerFunctions.JsonValueAsInt(m.Zweck, "$.original.id") > 2143 ); |
738 | ············qs = vt.QueryString; |
739 | ············Assert.AreEqual( qs, $"SELECT {this.reiseFields} FROM [Reise] WHERE JSON_VALUE([Reise].[Zweck], {{0}}) > {{1}}" ); |
740 | ············vt = pm.Objects<Reise>().Where( m => ServerFunctions.JsonValueAsString( m.Zweck, "$.original.someString" ) == "Hello" ); |
741 | ············qs = vt.QueryString; |
742 | ············Assert.AreEqual( qs, $"SELECT {this.reiseFields} FROM [Reise] WHERE JSON_VALUE([Reise].[Zweck], {{0}}) = {{1}}" ); |
743 | ············vt = pm.Objects<Reise>().Where( m => ServerFunctions.JsonValueAsString( m.Zweck, "$.original.someString" ).Like("Hello") ); |
744 | ············qs = vt.QueryString; |
745 | ············Assert.AreEqual( qs, $"SELECT {this.reiseFields} FROM [Reise] WHERE JSON_VALUE([Reise].[Zweck], {{0}}) LIKE {{1}}" ); |
746 | ········} |
747 | |
748 | |
749 | ········[Test] |
750 | ········public void GEWorks() |
751 | ········{ |
752 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.GreaterEqual( "abc" ) ); |
753 | ············var qs = vt.QueryString; |
754 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] >= {0}" ) > -1 ); |
755 | ········} |
756 | ········[Test] |
757 | ········public void LTWorks() |
758 | ········{ |
759 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.LowerThan( "abc" ) ); |
760 | ············var qs = vt.QueryString; |
761 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] < {0}" ) > -1 ); |
762 | ········} |
763 | ········[Test] |
764 | ········public void LEWorks() |
765 | ········{ |
766 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.LowerEqual("abc") ); |
767 | ············var qs = vt.QueryString; |
768 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] <= {0}" ) > -1 ); |
769 | ········} |
770 | |
771 | ········[Test] |
772 | ········public void GTWithByteArrayWorks() |
773 | ········{ |
774 | ············var arr = new byte[] { 1, 2, 3 }; |
775 | ············var vt = pm.Objects<DataContainer>().Where( m => m.ByteArrVar.GreaterThan( arr ) ); |
776 | ············var qs = vt.QueryString; |
777 | ············Assert.That( qs.IndexOf( "[DataContainer].[ByteArrVar] > {0}" ) > -1 ); |
778 | ········} |
779 | |
780 | ········[Test] |
781 | ········public void GEWithByteArrayWorks() |
782 | ········{ |
783 | ············var arr = new byte[] { 1, 2, 3 }; |
784 | ············var vt = pm.Objects<DataContainer>().Where( m => m.ByteArrVar.GreaterEqual( arr ) ); |
785 | ············var qs = vt.QueryString; |
786 | ············Assert.That( qs.IndexOf( "[DataContainer].[ByteArrVar] >= {0}" ) > -1 ); |
787 | ········} |
788 | ········[Test] |
789 | ········public void LTWithByteArrayWorks() |
790 | ········{ |
791 | ············var arr = new byte[] { 1, 2, 3 }; |
792 | ············var vt = pm.Objects<DataContainer>().Where( m => m.ByteArrVar.LowerThan( arr ) ); |
793 | ············var qs = vt.QueryString; |
794 | ············Assert.That( qs.IndexOf( "[DataContainer].[ByteArrVar] < {0}" ) > -1 ); |
795 | ········} |
796 | ········[Test] |
797 | ········public void LEWithByteArrayWorks() |
798 | ········{ |
799 | ············var arr = new byte[] { 1, 2, 3 }; |
800 | ············var vt = pm.Objects<DataContainer>().Where( m => m.ByteArrVar.LowerEqual( arr ) ); |
801 | ············var qs = vt.QueryString; |
802 | ············Assert.That( qs.IndexOf( "[DataContainer].[ByteArrVar] <= {0}" ) > -1 ); |
803 | ········} |
804 | |
805 | ········[Test] |
806 | ········public void GTDummyImplementationWithByteArrayWorks() |
807 | ········{ |
808 | ············var arr1 = new byte[] { 1, 2, 3, 4 }; |
809 | ············var arr2 = new byte[] { 1, 2, 3 }; |
810 | ············Assert.That( arr1.GreaterThan( arr2 ) ); |
811 | ············arr1 = new byte[] { 1, 3, 1 }; |
812 | ············arr2 = new byte[] { 1, 2, 3 }; |
813 | ············Assert.That( arr1.GreaterThan( arr2 ) ); |
814 | ············arr1 = new byte[] { 1, 2, 3 }; |
815 | ············arr2 = new byte[] { 1, 2, 3 }; |
816 | ············Assert.False( arr1.GreaterThan( arr2 ) ); |
817 | ········} |
818 | |
819 | ········[Test] |
820 | ········public void GEDummyImplementationWithByteArrayWorks() |
821 | ········{ |
822 | ············var arr1 = new byte[] { 1, 2, 3, 4 }; |
823 | ············var arr2 = new byte[] { 1, 2, 3 }; |
824 | ············Assert.That( arr1.GreaterEqual( arr2 ) ); |
825 | ············arr1 = new byte[] { 1, 3, 1 }; |
826 | ············arr2 = new byte[] { 1, 2, 3 }; |
827 | ············Assert.That( arr1.GreaterEqual( arr2 ) ); |
828 | ············arr1 = new byte[] { 1, 2, 3 }; |
829 | ············arr2 = new byte[] { 1, 2, 3 }; |
830 | ············Assert.That( arr1.GreaterEqual( arr2 ) ); |
831 | ········} |
832 | |
833 | ········[Test] |
834 | ········public void LTDummyImplementationWithByteArrayWorks() |
835 | ········{ |
836 | ············var arr1 = new byte[] { 1, 2, 3 }; |
837 | ············var arr2 = new byte[] { 1, 2, 3, 4 }; |
838 | ············Assert.That( arr1.LowerThan( arr2 ) ); |
839 | ············arr1 = new byte[] { 1, 2, 3 }; |
840 | ············arr2 = new byte[] { 1, 3, 1 }; |
841 | ············Assert.That( arr1.LowerThan( arr2 ) ); |
842 | ············arr1 = new byte[] { 1, 2, 3 }; |
843 | ············arr2 = new byte[] { 1, 2, 3 }; |
844 | ············Assert.False( arr1.LowerThan( arr2 ) ); |
845 | ········} |
846 | ········[Test] |
847 | ········public void LEDummyImplementationWithByteArrayWorks() |
848 | ········{ |
849 | ············var arr1 = new byte[] { 1, 2, 3 }; |
850 | ············var arr2 = new byte[] { 1, 2, 3, 4 }; |
851 | ············Assert.That( arr1.LowerEqual( arr2 ) ); |
852 | ············arr1 = new byte[] { 1, 2, 3 }; |
853 | ············arr2 = new byte[] { 1, 3, 1 }; |
854 | ············Assert.That( arr1.LowerEqual( arr2 ) ); |
855 | ············arr1 = new byte[] { 1, 2, 3 }; |
856 | ············arr2 = new byte[] { 1, 2, 3 }; |
857 | ············Assert.That( arr1.LowerEqual( arr2 ) ); |
858 | ········} |
859 | ····} |
860 | } |
861 |
New Commit (397e4c7)
1 | using System; |
2 | using System.Collections.Generic; |
3 | using System.Linq; |
4 | using NUnit.Framework; |
5 | using NDO; |
6 | using NDO.Query; |
7 | using NDO.Linq; |
8 | using Reisekosten; |
9 | using Reisekosten.Personal; |
10 | using PureBusinessClasses; |
11 | using NDO.SqlPersistenceHandling; |
12 | using DataTypeTestClasses; |
13 | using System.Linq.Expressions; |
14 | |
15 | namespace QueryTests |
16 | { |
17 | ····[TestFixture] |
18 | ····public class NDOLinqTests |
19 | ····{ |
20 | ········PersistenceManager pm; |
21 | ········string mitarbeiterFields; |
22 | ········string mitarbeiterJoinFields; |
23 | ········string belegFields; |
24 | ········string pkwFahrtFields; |
25 | ········string reiseFields; |
26 | ········string reiseJoinFields; |
27 | |
28 | ········[SetUp] |
29 | ········public void SetUp() |
30 | ········{ |
31 | ············this.pm = NDOFactory.Instance.PersistenceManager; |
32 | |
33 | ············mitarbeiterFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Mitarbeiter ) ) ).SelectList; |
34 | ············mitarbeiterJoinFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Mitarbeiter ) ) ).Result( false, false, true ); |
35 | ············belegFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Beleg ) ) ).SelectList; |
36 | ············this.pkwFahrtFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( PKWFahrt ) ) ).SelectList; |
37 | ············this.reiseFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Reise ) ) ).SelectList; |
38 | ············this.reiseJoinFields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Reise ) ) ).Result( false, false, true ); |
39 | ············Mitarbeiter m = new Mitarbeiter() { Vorname = "Mirko", Nachname = "Matytschak" }; |
40 | ············pm.MakePersistent( m ); |
41 | ············m = new Mitarbeiter() { Vorname = "Hans", Nachname = "Huber" }; |
42 | ············pm.MakePersistent( m ); |
43 | ············pm.Save(); |
44 | ········} |
45 | |
46 | ········[TearDown] |
47 | ········public void TearDown() |
48 | ········{ |
49 | ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm ); |
50 | ············this.pm.Delete( q.Execute() ); |
51 | ············this.pm.Save(); |
52 | ········} |
53 | |
54 | ········[Test] |
55 | ········public void CheckIfQueryWithoutWhereClauseWorks() |
56 | ········{ |
57 | ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm ); |
58 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter]", this.mitarbeiterFields ), q.GeneratedQuery ); |
59 | ········} |
60 | |
61 | ········[Test] |
62 | ········public void LinqCheckMitarbeiterQuery() |
63 | ········{ |
64 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>(); |
65 | ············string qs = vt.QueryString; |
66 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter]", qs ); |
67 | ········} |
68 | |
69 | ········[Test] |
70 | ········public void CheckIfOrderingsWork() |
71 | ········{ |
72 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>(); |
73 | ············vt.OrderBy( m => m.Vorname ).OrderByDescending( m => m.Nachname ); |
74 | ············string qs = vt.QueryString; |
75 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] ORDER BY [Mitarbeiter].[Vorname] ASC, [Mitarbeiter].[Nachname] DESC", qs ); |
76 | |
77 | ············vt = pm.Objects<Mitarbeiter>(); |
78 | ············vt.OrderBy( m => m.Vorname ).OrderByDescending( m => m.Nachname ); |
79 | ············vt.Take( 10 ).Skip( 12 ); |
80 | ············qs = vt.QueryString; |
81 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] ORDER BY [Mitarbeiter].[Vorname] ASC, [Mitarbeiter].[Nachname] DESC OFFSET 12 ROWS FETCH NEXT 10 ROWS ONLY", qs ); |
82 | ········} |
83 | |
84 | ········[Test] |
85 | ········public void LinqTestIfSimpleWhereClauseWorks() |
86 | ········{ |
87 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname == "Mirko" ); |
88 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}}", vt.QueryString ); |
89 | ············// Query for Oid values |
90 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Oid() == 5 ); |
91 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[ID] = {{0}}", vt.QueryString ); |
92 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.NDOObjectId == 5 ); |
93 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[ID] = {{0}}", vt.QueryString ); |
94 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Oid().Equals( 5 ) ); |
95 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[ID] = {{0}}", vt.QueryString ); |
96 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.NDOObjectId.Equals( 5 ) ); |
97 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[ID] = {{0}}", vt.QueryString ); |
98 | ········} |
99 | |
100 | ········[Test] |
101 | ········public void LinqCheckIfGeneratedQueryCanBeCalledTwice() |
102 | ········{ |
103 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname == "Mirko" ); |
104 | |
105 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}}", vt.QueryString ); |
106 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}}", vt.QueryString ); |
107 | ········} |
108 | |
109 | ········[Test] |
110 | ········public void LinqParameterChangesDontChangeTheQuery() |
111 | ········{ |
112 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname == "Mirko" ); |
113 | |
114 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}}", vt.QueryString ); |
115 | |
116 | ············vt.ReplaceParameters( new object[] { "Hans" } ); |
117 | |
118 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}}", vt.QueryString ); |
119 | ········} |
120 | |
121 | ········[Test] |
122 | ········public void LinqCheckIfWhereClauseWith1nRelationWorks() |
123 | ········{ |
124 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Zweck == "ADC" ); |
125 | ············string qs = vt.QueryString; |
126 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[Zweck] = {{0}}", qs ); |
127 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Oid().Equals( 5 ) ); |
128 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
129 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Oid() == 5 ); |
130 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
131 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].NDOObjectId.Equals( 5 ) ); |
132 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
133 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].NDOObjectId == 5 ); |
134 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
135 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].NDOObjectId.In(new int[] { 1, 2, 3 } ) ); |
136 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] IN (1, 2, 3)", this.mitarbeiterJoinFields ), vt.QueryString ); |
137 | ········} |
138 | |
139 | ········[Test] |
140 | ········public void LinqCheckIfWhereClauseWithAnyIn1nRelationWorks() |
141 | ········{ |
142 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen.Any(r=>r.Zweck == "ADC") ); |
143 | ············string qs = vt.QueryString; |
144 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[Zweck] = {{0}}", qs ); |
145 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen.Any( r => r.Oid().Equals( 5 ) ) ); |
146 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
147 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen.Any( r => r.Oid() == 5 ) ); |
148 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
149 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen.Any( r => r.NDOObjectId.Equals( 5 ) ) ); |
150 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
151 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen.Any( r => r.NDOObjectId == 5 ) ); |
152 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
153 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen.Any( r => r.NDOObjectId.In( new int[] { 1, 2, 3 } ) ) ); |
154 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] IN (1, 2, 3)", this.mitarbeiterJoinFields ), vt.QueryString ); |
155 | ········} |
156 | |
157 | |
158 | ········[Test] |
159 | ········public void LinqCheckIfWhereClauseWith11RelationWorks() |
160 | ········{ |
161 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse.Lkz.Like( "D%" ) ); |
162 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Adresse] ON [Adresse].[ID] = [Mitarbeiter].[IDAdresse] WHERE [Adresse].[Lkz] LIKE {{0}}", vt.QueryString ); |
163 | ········} |
164 | |
165 | ········[Test] |
166 | ········public void LinqCheckIfWhereClauseWithOidIn11RelationWorks() |
167 | ········{ |
168 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse.Oid() == 5 ); |
169 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[IDAdresse] = {{0}}", vt.QueryString ); |
170 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse.Oid().In(new[]{ 1, 2, 3 } ) ); |
171 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[IDAdresse] IN (1, 2, 3)", vt.QueryString ); |
172 | ········} |
173 | |
174 | ········[Test] |
175 | ········public void LinqCheckIfMultipleRelationsWork() |
176 | ········{ |
177 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse.Lkz.Like( "D%" ) && m.Reisen[Any.Index].Länder[Any.Index].Name == "D" ); |
178 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Adresse] ON [Adresse].[ID] = [Mitarbeiter].[IDAdresse] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] INNER JOIN [relLandReise] ON [Reise].[ID] = [relLandReise].[IDReise] INNER JOIN [Land] ON [Land].[ID] = [relLandReise].[IDLand] WHERE [Adresse].[Lkz] LIKE {{0}} AND [Land].[Name] = {{1}}", vt.QueryString ); |
179 | ········} |
180 | |
181 | ········[Test] |
182 | ········public void LinqCheckOidWithTable() |
183 | ········{ |
184 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Länder[Any.Index].Oid() == 55 ); |
185 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] INNER JOIN [relLandReise] ON [Reise].[ID] = [relLandReise].[IDReise] WHERE [relLandReise].[IDLand] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
186 | ········} |
187 | |
188 | ········[Test] |
189 | ········public void LinqCheckThatOneJoinAppearsOnlyOneTime() |
190 | ········{ |
191 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse.Lkz.Like("D%") && m.Adresse.Ort != "Bad Tölz" ); |
192 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Adresse] ON [Adresse].[ID] = [Mitarbeiter].[IDAdresse] WHERE [Adresse].[Lkz] LIKE {{0}} AND [Adresse].[Ort] <> {{1}}", vt.QueryString ); |
193 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Zweck == "ADC" || m.Reisen[Any.Index].Zweck == "ADW" ); |
194 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[Zweck] = {{0}} OR [Reise].[Zweck] = {{1}}", vt.QueryString ); |
195 | ········} |
196 | |
197 | ········[Test] |
198 | ········public void LinqCheckNotOperator() |
199 | ········{ |
200 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where(m => !(m.Nachname == "Matytschak") ); |
201 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE NOT ([Mitarbeiter].[Nachname] = {{0}})", vt.QueryString ); |
202 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.Like( "M%" ) && !(m.Nachname == "Matytschak") ); |
203 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] LIKE {{0}} AND NOT ([Mitarbeiter].[Nachname] = {{1}})", vt.QueryString ); |
204 | ············vt = pm.Objects<Mitarbeiter>().Where( m => !(m.Vorname.Like( "M%" ) && m.Nachname == "Matytschak") ); |
205 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE NOT ([Mitarbeiter].[Vorname] LIKE {{0}} AND [Mitarbeiter].[Nachname] = {{1}})", vt.QueryString ); |
206 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Zweck == "ADC" || !(m.Reisen[Any.Index].Zweck == "ADW") ); |
207 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[Zweck] = {{0}} OR NOT ([Reise].[Zweck] = {{1}})", vt.QueryString ); |
208 | ············vt = pm.Objects<Mitarbeiter>().Where( m => !(m.Reisen[Any.Index].Zweck == "ADC" || m.Reisen[Any.Index].Zweck == "ADW") ); |
209 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE NOT ([Reise].[Zweck] = {{0}} OR [Reise].[Zweck] = {{1}})", vt.QueryString ); |
210 | ············vt = pm.Objects<Mitarbeiter>().Where( m => !(m.Reisen[Any.Index].Länder[Any.Index].IsInEu == true) ); |
211 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] INNER JOIN [relLandReise] ON [Reise].[ID] = [relLandReise].[IDReise] INNER JOIN [Land] ON [Land].[ID] = [relLandReise].[IDLand] WHERE NOT ([Land].[IsInEu] = {{0}})", vt.QueryString ); |
212 | ········} |
213 | |
214 | ········[Test] |
215 | ········public void LinqCheckBetween() |
216 | ········{ |
217 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.Between( "A", "B" ) ); |
218 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] BETWEEN {{0}} AND {{1}}", vt.QueryString ); |
219 | ············vt = pm.Objects<Mitarbeiter>().Where( m => !m.Vorname.Between( "A", "B" ) ); |
220 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE NOT [Mitarbeiter].[Vorname] BETWEEN {{0}} AND {{1}}", vt.QueryString ); |
221 | ········} |
222 | |
223 | ········[Test] |
224 | ········public void LinqTestValueType() |
225 | ········{ |
226 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Position.X > 2 && m.Position.Y < 5); |
227 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Position_X] > {{0}} AND [Mitarbeiter].[Position_Y] < {{1}}", vt.QueryString ); |
228 | ········} |
229 | |
230 | ········[Test] |
231 | ········public void TestValueTypeRelation() |
232 | ········{ |
233 | ············var vt = pm.Objects<Sozialversicherungsnummer>().Where( s => s.Angestellter.Position.X > 2 && s.Angestellter.Position.Y < 5 ); |
234 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Sozialversicherungsnummer ) ) ).Result( false, false, true ); |
235 | ············Assert.AreEqual( $"SELECT {fields} FROM [Sozialversicherungsnummer] INNER JOIN [Mitarbeiter] ON [Mitarbeiter].[ID] = [Sozialversicherungsnummer].[IDSozial] WHERE [Mitarbeiter].[Position_X] > {{0}} AND [Mitarbeiter].[Position_Y] < {{1}}", vt.QueryString ); |
236 | ········} |
237 | |
238 | ········[Test] |
239 | ········public void LinqCheckFetchGroupInitializationWithExpressions() |
240 | ········{ |
241 | ············//TODO: Clarify how this should be implemented in Linq |
242 | ············//FetchGroup<Mitarbeiter> fg = new FetchGroup<Mitarbeiter>( m => m.Vorname, m => m.Nachname ); |
243 | ············//Assert.AreEqual( fg.Count, 2, "Count should be 2" ); |
244 | ············//Assert.AreEqual( "Vorname", fg[0], "Name wrong #1" ); |
245 | ············//Assert.AreEqual( "Nachname", fg[1], "Name wrong #2" ); |
246 | ········} |
247 | |
248 | ········[Test] |
249 | ········public void LinqCheckIfMultiKeysWork() |
250 | ········{ |
251 | ············var orderDetail = new OrderDetail(); |
252 | ············var vt = pm.Objects<OrderDetail>().Where( od => od.Oid() == orderDetail.NDOObjectId ); |
253 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( OrderDetail ) ) ).SelectList; |
254 | ············Assert.AreEqual( $"SELECT {fields} FROM [OrderDetail] WHERE [OrderDetail].[IDProduct] = {{0}} AND [OrderDetail].[IDOrder] = {{1}}", vt.QueryString ); |
255 | #if ignored |
256 | ············bool thrown = false; |
257 | ············try |
258 | ············{ |
259 | ················vt = pm.Objects<OrderDetail>().Where( od => od.Oid() == -4 ); |
260 | ················string s = vt.QueryString; |
261 | ············} |
262 | ············catch (Exception) |
263 | ············{ |
264 | ················thrown = true; |
265 | ············} |
266 | ············// This fails, because the parameter won't be checked by the parser. |
267 | ············// It isn't checked in the WherePart-Generator neither because it checks only, if the right side of the comparism is a parameter. |
268 | ············// We need to check the oid mapping to detect this situation. |
269 | ············// Or we might let it be, because we will get an exception anyway, if the query is executed. |
270 | ············Assert.AreEqual( true, thrown ); |
271 | #endif |
272 | ········} |
273 | |
274 | ········[Test] |
275 | ········public void LinqTestBooleanExpression() |
276 | ········{ |
277 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Land ) ) ).SelectList; |
278 | ············var vt = pm.Objects<Land>().Where( l => l.IsInEu == true ); |
279 | ············Assert.AreEqual( $"SELECT {fields} FROM [Land] WHERE [Land].[IsInEu] = {{0}}", vt.QueryString ); |
280 | |
281 | ············vt = pm.Objects<Land>().Where( l => l.IsInEu ); |
282 | ············Assert.AreEqual( $"SELECT {fields} FROM [Land] WHERE [Land].[IsInEu] = 1", vt.QueryString ); |
283 | |
284 | ············vt = pm.Objects<Land>().Where( l => l.IsInEu && l.Name == "Lala" ); |
285 | ············Assert.AreEqual( $"SELECT {fields} FROM [Land] WHERE [Land].[IsInEu] = 1 AND [Land].[Name] = {{0}}", vt.QueryString ); |
286 | |
287 | ············vt = pm.Objects<Land>().Where( l => l.Name == "Lala" && l.IsInEu ); |
288 | ············Assert.AreEqual( $"SELECT {fields} FROM [Land] WHERE [Land].[IsInEu] = 1 AND [Land].[Name] = {{0}}", vt.QueryString ); |
289 | ········} |
290 | |
291 | ········[Test] |
292 | ········public void LinqTestSuperclasses() |
293 | ········{ |
294 | ············var vt = pm.Objects<Kostenpunkt>(); |
295 | ············Assert.AreEqual( $"SELECT {this.belegFields} FROM [Beleg];\r\nSELECT {this.pkwFahrtFields} FROM [PKWFahrt]", vt.QueryString ); |
296 | ········} |
297 | |
298 | ········[Test] |
299 | ········public void CanAddPrefetches() |
300 | ········{ |
301 | ············var vt = pm.Objects<Mitarbeiter>(); |
302 | ············vt.AddPrefetch( m => m.Reisen ); |
303 | ············vt.AddPrefetch( m => m.Reisen[Any.Index].Länder ); |
304 | ············var list = vt.Prefetches.ToList(); |
305 | ············Assert.AreEqual( 2, list.Count ); |
306 | ············Assert.AreEqual( "Reisen", list[0] ); |
307 | ············Assert.AreEqual( "Reisen.Länder", list[1] ); |
308 | ········} |
309 | |
310 | ········[Test] |
311 | ········public void LinqSimplePrefetchWorks() |
312 | ········{ |
313 | ············var vt = pm.Objects<Mitarbeiter>(); |
314 | ············vt.AddPrefetch( m => m.Reisen ); |
315 | ············var s = vt.QueryString; |
316 | ········} |
317 | |
318 | ········[Test, Ignore("This is not implemented")] |
319 | ········public void LinqPrefetchWithBidirectionalRelationWorks() |
320 | ········{ |
321 | ············Assert.That( false, "Not implemented" ); |
322 | ············// With Bidirectional Relation (vorhandener JOIN) |
323 | ········} |
324 | |
325 | ········[Test, Ignore( "This is not implemented" )] |
326 | ········public void LinqPrefetchWithMonoRelationWorks() |
327 | ········{ |
328 | ············Assert.That( false, "Not implemented" ); |
329 | ············// Monodirektional (neuer JOIN) |
330 | ········} |
331 | |
332 | ········[Test, Ignore( "This is not implemented" )] |
333 | ········public void LinqPrefetchWithDifferentRelationRolesWorks() |
334 | ········{ |
335 | ············Assert.That( false, "Not implemented" ); |
336 | ············// Unterschiedliche Relationen werden auseinandergehalten |
337 | ········} |
338 | |
339 | ········[Test] |
340 | ········public void LinqTestPolymorphicRelationQueries() |
341 | ········{ |
342 | ············// We have to patch the AccessorName here, since the Enhancer doesn't create the AccessorName automatically. |
343 | ············// This will change as soon as we update the tests. |
344 | ············pm.NDOMapping.FindClass( typeof( Kostenpunkt ) ).FindField( "datum" ).AccessorName = "Datum"; |
345 | ············pm.NDOMapping.FindClass( typeof( Beleg ) ).FindField( "datum" ).AccessorName = "Datum"; |
346 | ············pm.NDOMapping.FindClass( typeof( PKWFahrt ) ).FindField( "datum" ).AccessorName = "Datum"; |
347 | |
348 | ············var vt = pm.Objects<Reise>().Where(r => r.Kostenpunkte[Any.Index].Datum == DateTime.Now.Date); |
349 | |
350 | ············Assert.AreEqual( $"SELECT {reiseJoinFields} FROM [Reise] INNER JOIN [relBelegKostenpunkt] ON [Reise].[ID] = [relBelegKostenpunkt].[IDReise] INNER JOIN [Beleg] ON [Beleg].[ID] = [relBelegKostenpunkt].[IDBeleg] AND [relBelegKostenpunkt].[TCBeleg] = 926149172 WHERE [Beleg].[Datum] = {{0}} UNION \r\nSELECT {reiseJoinFields} FROM [Reise] INNER JOIN [relBelegKostenpunkt] ON [Reise].[ID] = [relBelegKostenpunkt].[IDReise] INNER JOIN [PKWFahrt] ON [PKWFahrt].[ID] = [relBelegKostenpunkt].[IDBeleg] AND [relBelegKostenpunkt].[TCBeleg] = 734406058 WHERE [PKWFahrt].[Datum] = {{0}}", vt.QueryString ); |
351 | ········} |
352 | |
353 | ········[Test] |
354 | ········public void LinqTest1To1() |
355 | ········{ |
356 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.Zimmer.Zimmer == "abc"); |
357 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Buero] ON [Buero].[ID] = [Mitarbeiter].[IDBuero] WHERE [Buero].[Zimmer] = {{0}}", vt.QueryString ); |
358 | ········} |
359 | |
360 | |
361 | ········[Test] |
362 | ········public void LinqTest1To1Bidirectional() |
363 | ········{ |
364 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.SVN.SVN == 4711); |
365 | ············NDOQuery<Mitarbeiter> q = new NDOQuery<Mitarbeiter>( pm, "sn.nummer = 'abc'" ); |
366 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Sozialversicherungsnummer] ON [Sozialversicherungsnummer].[ID] = [Mitarbeiter].[IDSozial] WHERE [Sozialversicherungsnummer].[Nummer] = 'abc'", this.mitarbeiterJoinFields ), q.GeneratedQuery ); |
367 | ············var vt2 = pm.Objects<Sozialversicherungsnummer>().Where(s=>s.Angestellter.Vorname == "Mirko"); |
368 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Sozialversicherungsnummer ) ) ).Result( false, false, true ); |
369 | ············Assert.AreEqual( $"SELECT {fields} FROM [Sozialversicherungsnummer] INNER JOIN [Mitarbeiter] ON [Mitarbeiter].[ID] = [Sozialversicherungsnummer].[IDSozial] WHERE [Mitarbeiter].[Vorname] = {{0}}", vt2.QueryString ); |
370 | ········} |
371 | |
372 | ········[Test] |
373 | ········public void LinqTest1To1BiWithTable() |
374 | ········{ |
375 | ············var vt1 = pm.Objects<Zertifikat>().Where(z=>z.SGN.Key == "abc"); |
376 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Zertifikat ) ) ).Result( false, false, true ); |
377 | ············Assert.AreEqual( $"SELECT {fields} FROM [Zertifikat] INNER JOIN [relSignaturZertifikat] ON [Zertifikat].[ID] = [relSignaturZertifikat].[IDZertifikat] INNER JOIN [Signatur] ON [Signatur].[ID] = [relSignaturZertifikat].[IDSignatur] WHERE [Signatur].[Signature] = {{0}}", vt1.QueryString ); |
378 | ············var vt2 = pm.Objects<Signatur>().Where(sg=>sg.Owner.Key == -4); |
379 | ············fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( Signatur ) ) ).Result( false, false, true ); |
380 | ············Assert.AreEqual( $"SELECT {fields} FROM [Signatur] INNER JOIN [relSignaturZertifikat] ON [Signatur].[ID] = [relSignaturZertifikat].[IDSignatur] INNER JOIN [Zertifikat] ON [Zertifikat].[ID] = [relSignaturZertifikat].[IDZertifikat] WHERE [Zertifikat].[Schlüssel] = {{0}}", vt2.QueryString); |
381 | ········} |
382 | |
383 | ········[Test] |
384 | ········public void LinqTest1ToNWithTable() |
385 | ········{ |
386 | ············// We have to patch the AccessorName here, since the Enhancer doesn't create the AccessorName automatically. |
387 | ············// This will change as soon as we update the tests. |
388 | ············pm.NDOMapping.FindClass( typeof( Mitarbeiter ) ).FindRelation( "reiseBüros" ).AccessorName = "ReiseBüros"; |
389 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.ReiseBüros.ElementAt(Any.Index).Name == "abc"); |
390 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [relMitarbeiterReisebuero] ON [Mitarbeiter].[ID] = [relMitarbeiterReisebuero].[IDMitarbeiter] INNER JOIN [Reisebuero] ON [Reisebuero].[ID] = [relMitarbeiterReisebuero].[IDReisebuero] WHERE [Reisebuero].[Name] = {{0}}", vt.QueryString ); |
391 | ········} |
392 | |
393 | ········[Test] |
394 | ········public void LinqTestIfQueryForNonNullOidsWorks() |
395 | ········{ |
396 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Länder.Oid() != null ); |
397 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] INNER JOIN [relLandReise] ON [Reise].[ID] = [relLandReise].[IDReise] WHERE [relLandReise].[IDLand] IS NOT NULL", vt.QueryString ); |
398 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Länder[Any.Index].NDOObjectId != null ); |
399 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] INNER JOIN [relLandReise] ON [Reise].[ID] = [relLandReise].[IDReise] WHERE [relLandReise].[IDLand] IS NOT NULL", vt.QueryString ); |
400 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse.NDOObjectId != null ); |
401 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[IDAdresse] IS NOT NULL", vt.QueryString ); |
402 | ········} |
403 | |
404 | ········[Test] |
405 | ········public void LinqTestIfQueryWithNonNullRelationWorks() |
406 | ········{ |
407 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse != null ); |
408 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[IDAdresse] IS NOT NULL", vt.QueryString ); |
409 | ········} |
410 | |
411 | ········[Test] |
412 | ········public void LinqTestIfQueryWithNullRelationWorks() |
413 | ········{ |
414 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Adresse == null ); |
415 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[IDAdresse] IS NULL", vt.QueryString ); |
416 | ········} |
417 | |
418 | ········[Test] |
419 | ········public void LinqTestIfIsNullWithStringWorks() |
420 | ········{ |
421 | ············var sql = $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IS NULL"; |
422 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname == null ); |
423 | ············Assert.AreEqual( sql, vt.QueryString ); |
424 | ············vt = pm.Objects<Mitarbeiter>().Where( m => null == m.Vorname ); |
425 | ············Assert.AreEqual( sql, vt.QueryString ); |
426 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.Equals(null) ); |
427 | ············Assert.AreEqual( sql, vt.QueryString ); |
428 | ········} |
429 | |
430 | ········[Test] |
431 | ········public void CanCombineExpressions() |
432 | ········{ |
433 | ············var sql = $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] = {{0}} AND [Mitarbeiter].[Nachname] = {{1}}"; |
434 | ············Expression<Func<Mitarbeiter,bool>> expr1 = m=>m.Vorname == "Mirko"; |
435 | ············Expression<Func<Mitarbeiter,bool>> expr2 = m=>m.Nachname == "Matytschak"; |
436 | ············var combined = expr1.Combine(expr2, System.Linq.Expressions.ExpressionType.And); |
437 | ············var vt = pm.Objects<Mitarbeiter>().Where( combined ); |
438 | ············Assert.AreEqual( sql, vt.QueryString ); |
439 | ········} |
440 | |
441 | |
442 | ········[Test] |
443 | ········public void LinqTestIfIsNotNullWithStringWorks() |
444 | ········{ |
445 | ············var sql = $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IS NOT NULL"; |
446 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname != null ); |
447 | ············Assert.AreEqual( sql, vt.QueryString ); |
448 | ············vt = pm.Objects<Mitarbeiter>().Where( m => null != m.Vorname ); |
449 | ············Assert.AreEqual( sql, vt.QueryString ); |
450 | ············vt = pm.Objects<Mitarbeiter>().Where( m => !m.Vorname.Equals( null ) ); |
451 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE NOT [Mitarbeiter].[Vorname] IS NULL", vt.QueryString ); |
452 | ········} |
453 | |
454 | ········[Test] |
455 | ········public void LinqTestIfIsNullWithGuidWorks() |
456 | ········{ |
457 | ············// The query will fetch for DataContainerDerived objects, too. We need to define the Accessor on-the-fly for this class, since |
458 | ············// the accessor isn't defined in the original mapping file. |
459 | ············// We also test for "StartsWith", because the query contains additional text, which doesn't matter here. |
460 | ············pm.NDOMapping.FindClass( typeof( DataContainerDerived ) ).FindField( "guidVar" ).AccessorName = "GuidVar"; |
461 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( DataContainer ) ) ).SelectList; |
462 | ············var sql = $"SELECT {fields} FROM [DataContainer] WHERE [DataContainer].[GuidVar] IS NULL"; |
463 | ············var vt = pm.Objects<DataContainer>().Where( m => m.GuidVar == null ); |
464 | ············Assert.That( vt.QueryString.StartsWith( sql) ); |
465 | ············vt = pm.Objects<DataContainer>().Where( m => m.GuidVar == Guid.Empty ); |
466 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
467 | ············vt = pm.Objects<DataContainer>().Where( m => Guid.Empty == m.GuidVar ); |
468 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
469 | ············vt = pm.Objects<DataContainer>().Where( m => m.GuidVar.Equals(Guid.Empty) ); |
470 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
471 | ········} |
472 | |
473 | ········[Test] |
474 | ········public void LinqTestIfIsNotNullWithGuidWorks() |
475 | ········{ |
476 | ············// The query will fetch for DataContainerDerived objects, too. We need to define the Accessor on-the-fly for this class, since |
477 | ············// the accessor isn't defined in the original mapping file. |
478 | ············// We also test for "StartsWith", because the query contains additional text, which doesn't matter here. |
479 | ············pm.NDOMapping.FindClass( typeof( DataContainerDerived ) ).FindField( "guidVar" ).AccessorName = "GuidVar"; |
480 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( DataContainer ) ) ).SelectList; |
481 | ············var sql = $"SELECT {fields} FROM [DataContainer] WHERE [DataContainer].[GuidVar] IS NOT NULL"; |
482 | ············var vt = pm.Objects<DataContainer>().Where( m => m.GuidVar != null ); |
483 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
484 | ············vt = pm.Objects<DataContainer>().Where( m => m.GuidVar != Guid.Empty ); |
485 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
486 | ············vt = pm.Objects<DataContainer>().Where( m => Guid.Empty != m.GuidVar ); |
487 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
488 | ············vt = pm.Objects<DataContainer>().Where( m => !m.GuidVar.Equals( Guid.Empty ) ); |
489 | ············Assert.That( vt.QueryString.StartsWith( $"SELECT {fields} FROM [DataContainer] WHERE NOT [DataContainer].[GuidVar] IS NULL" ) ); |
490 | ········} |
491 | |
492 | ········[Test] |
493 | ········public void LinqTestIfIsNullWithDateTimeWorks() |
494 | ········{ |
495 | ············// The query will fetch for DataContainerDerived objects, too. We need to define the Accessor on-the-fly for this class, since |
496 | ············// the accessor isn't defined in the original mapping file. |
497 | ············// We also test for "StartsWith", because the query contains additional text, which doesn't matter here. |
498 | ············pm.NDOMapping.FindClass( typeof( DataContainerDerived ) ).FindField( "dateTimeVar" ).AccessorName = "DateTimeVar"; |
499 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( DataContainer ) ) ).SelectList; |
500 | ············var sql = $"SELECT {fields} FROM [DataContainer] WHERE [DataContainer].[DateTimeVar] IS NULL"; |
501 | ············var vt = pm.Objects<DataContainer>().Where( m => m.DateTimeVar == null ); |
502 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
503 | ············vt = pm.Objects<DataContainer>().Where( m => m.DateTimeVar == DateTime.MinValue ); |
504 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
505 | ············vt = pm.Objects<DataContainer>().Where( m => DateTime.MinValue == m.DateTimeVar ); |
506 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
507 | ············vt = pm.Objects<DataContainer>().Where( m => m.DateTimeVar.Equals(DateTime.MinValue) ); |
508 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
509 | ········} |
510 | |
511 | ········[Test] |
512 | ········public void LinqTestIfIsNotNullWithDateTimeWorks() |
513 | ········{ |
514 | ············// The query will fetch for DataContainerDerived objects, too. We need to define the Accessor on-the-fly for this class, since |
515 | ············// the accessor isn't defined in the original mapping file. |
516 | ············// We also test for "StartsWith", because the query contains additional text, which doesn't matter here. |
517 | ············pm.NDOMapping.FindClass( typeof( DataContainerDerived ) ).FindField( "dateTimeVar" ).AccessorName = "DateTimeVar"; |
518 | ············var fields = new SqlColumnListGenerator( pm.NDOMapping.FindClass( typeof( DataContainer ) ) ).SelectList; |
519 | ············var sql = $"SELECT {fields} FROM [DataContainer] WHERE [DataContainer].[DateTimeVar] IS NOT NULL"; |
520 | ············var vt = pm.Objects<DataContainer>().Where( m => m.DateTimeVar != null ); |
521 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
522 | ············vt = pm.Objects<DataContainer>().Where( m => m.DateTimeVar != DateTime.MinValue ); |
523 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
524 | ············vt = pm.Objects<DataContainer>().Where( m => DateTime.MinValue != m.DateTimeVar ); |
525 | ············Assert.That( vt.QueryString.StartsWith( sql ) ); |
526 | ············vt = pm.Objects<DataContainer>().Where( m => !m.DateTimeVar.Equals( DateTime.MinValue ) ); |
527 | ············Assert.That( vt.QueryString.StartsWith( $"SELECT {fields} FROM [DataContainer] WHERE NOT [DataContainer].[DateTimeVar] IS NULL" ) ); |
528 | ········} |
529 | |
530 | ········[Test] |
531 | ········public void LinqTestIfInClauseWorks() |
532 | ········{ |
533 | ············// Note: Single quotes must be escaped to avoid Sql injection attacks |
534 | ············var arr = new[] { "Mirko", "Hans'" }; |
535 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.Vorname.In(arr));············ |
536 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IN ('Mirko', 'Hans''')", vt.QueryString ); |
537 | ············vt = pm.Objects<Mitarbeiter>().Where(m => m.Vorname.In(new[] { "Mirko", "Ha'ns" })); |
538 | ············Assert.AreEqual($"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IN ('Mirko', 'Ha''ns')", vt.QueryString); |
539 | ············arr = new[] { "Mirko" }; |
540 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.In( arr ) ); |
541 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IN ('Mirko')", vt.QueryString ); |
542 | ········} |
543 | |
544 | ········[Test] |
545 | ········public void LinqTestIfInClauseWithNumbersWorks() |
546 | ········{ |
547 | ············// NDO won't check, if the types match |
548 | ············var arr = new[] { 1,2,3,4,5 }; |
549 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.Vorname.In(arr)); |
550 | Assert. AreEqual( $"SELECT { this. mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter]. [Vorname] IN ( 1, 2, 3, 4, 5) ", vt. QueryString ) ; |
551 | ············vt = pm.Objects<Mitarbeiter>().Where( m => !m.Oid().In( arr ) ); |
552 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE NOT [Mitarbeiter].[ID] IN (1, 2, 3, 4, 5)", vt.QueryString ); |
553 | ············arr = new[] { 333 }; |
554 | ············vt = pm.Objects<Mitarbeiter>().Where( m => !m.Oid().In( arr ) ); |
555 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE NOT [Mitarbeiter].[ID] IN (333)", vt.QueryString ); |
556 | ········} |
557 | |
558 | ········[Test] |
559 | ········public void LinqTestIfInClauseWithGuidsWorks() |
560 | ········{ |
561 | ············var guids = new Guid[]{ Guid.NewGuid(), Guid.NewGuid() }; |
562 | ············// NDO won't check, if the types match |
563 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.Vorname.In(guids)); |
564 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IN ('{guids[0]}', '{guids[1]}')", vt.QueryString ); |
565 | ········} |
566 | |
567 | ········[Test] |
568 | ········public void LinqTestIfRelationInInClauseWorks() |
569 | ········{ |
570 | ············var arr = new[] { 1, 2, 3, 4, 5 }; |
571 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.Reisen.Oid().In(arr)); |
572 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] IN (1, 2, 3, 4, 5)", vt.QueryString ); |
573 | ············vt = pm.Objects<Mitarbeiter>().Where(m => m.Reisen.Oid().In(new[] { 1, 2, 3, 4, 5 })); |
574 | ············Assert.AreEqual($"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] IN (1, 2, 3, 4, 5)", vt.QueryString); |
575 | ········} |
576 | |
577 | ········[Test] |
578 | ········public void TestIfOidWithInClauseWorks() |
579 | ········{ |
580 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => m.Oid().In(new[] { 1, 2, 3, 4, 5 })); |
581 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[ID] IN (1, 2, 3, 4, 5)", vt.QueryString ); |
582 | ········} |
583 | |
584 | ········[Test] |
585 | ········public void TestIfLinqQueryWithOidParameterWorks() |
586 | ········{ |
587 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Oid(0).Equals( 5 ) ); |
588 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
589 | ············vt = pm.Objects<Mitarbeiter>().Where( m => (int)m.Reisen[Any.Index].NDOObjectId[0] == 5 ); |
590 | ············Assert.AreEqual( String.Format( "SELECT {0} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] = {{0}}", this.mitarbeiterJoinFields ), vt.QueryString ); |
591 | ········} |
592 | |
593 | ········[Test] |
594 | ········public void TestIfLinqQueryForNonNullOidsWorks() |
595 | ········{ |
596 | ············VirtualTable<Mitarbeiter> vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Oid() != null ); |
597 | ············var qs = vt.QueryString; |
598 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] WHERE [Reise].[ID] IS NOT NULL", qs ); |
599 | ············vt = pm.Objects<Mitarbeiter>().Where( m => m.Reisen[Any.Index].Länder[Any.Index].Oid() != null ); |
600 | ············qs = vt.QueryString; |
601 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterJoinFields} FROM [Mitarbeiter] INNER JOIN [Reise] ON [Mitarbeiter].[ID] = [Reise].[IDMitarbeiter] INNER JOIN [relLandReise] ON [Reise].[ID] = [relLandReise].[IDReise] WHERE [relLandReise].[IDLand] IS NOT NULL", qs ); |
602 | ········} |
603 | |
604 | ········[Test] |
605 | ········public void ComparismWithStringWorks() |
606 | ········{ |
607 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.GreaterEqual( "abc" ) && m.Vorname.LowerThan( "abcd") ); |
608 | ············var qs = vt.QueryString; |
609 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] >= {0}" ) > -1 ); |
610 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] < {1}" ) > -1 ); |
611 | ········} |
612 | |
613 | ········[Test] |
614 | ········public void ComparismWithEmptyStringWorks() |
615 | ········{ |
616 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.GreaterEqual( String.Empty ) && m.Vorname == String.Empty ); |
617 | ············var qs = vt.QueryString; |
618 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] >= {0}" ) > -1 ); |
619 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] = {0}" ) > -1 ); |
620 | ············// We compare with String.Empty two times. The ExpressionTreeTransformer recognizes the equality and |
621 | ············// creates only one parameter. |
622 | ········} |
623 | |
624 | ········[Test] |
625 | ········public void CanFetchForNotStringIsNullOrEmpty() |
626 | ········{ |
627 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname != null && m.Vorname != String.Empty ); |
628 | ············var qs = vt.QueryString; |
629 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] IS NOT NULL AND [Mitarbeiter].[Vorname] <> {{0}}", vt.QueryString ); |
630 | ········} |
631 | |
632 | ········[Test] |
633 | ········public void CanUseStringsInINClauses() |
634 | ········{ |
635 | ············List<string> strings = new List<string>{ "1", "2", "3" }; |
636 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.Like("Hallo") && !m.Nachname.In( strings ) ); |
637 | ············var qs = vt.QueryString; |
638 | ············Assert.AreEqual( $"SELECT {this.mitarbeiterFields} FROM [Mitarbeiter] WHERE [Mitarbeiter].[Vorname] LIKE {{0}} AND NOT [Mitarbeiter].[Nachname] IN ('1', '2', '3')", vt.QueryString ); |
639 | ········} |
640 | |
641 | |
642 | |
643 | ········[Test] |
644 | ········public void ComparismBetweenTwoFieldWorks() |
645 | ········{ |
646 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.GreaterEqual( m.Nachname ) ); |
647 | ············var qs = vt.QueryString; |
648 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] >= [Mitarbeiter].[Nachname]" ) > -1 ); |
649 | ········} |
650 | |
651 | ········[Test] |
652 | ········public void FlipParametersWorks() |
653 | ········{ |
654 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => "abc" == m.Vorname ); |
655 | ············var qs = vt.QueryString; |
656 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] = {0}" ) > -1 ); |
657 | ········} |
658 | |
659 | ········[Test] |
660 | ········public void FlipParametersInComplexExpressionsWorks() |
661 | ········{ |
662 | ············var vt = pm.Objects<Mitarbeiter>().Where(m => "abc" == m.Vorname && "def" == m.Nachname ); |
663 | ············var qs = vt.QueryString; |
664 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] = {0}" ) > -1 ); |
665 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Nachname] = {1}" ) > -1 ); |
666 | ········} |
667 | |
668 | ········[Test] |
669 | ········public void FlipParametersWithGTWorks() |
670 | ········{ |
671 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => "abc".GreaterThan(m.Vorname) ); |
672 | ············var qs = vt.QueryString; |
673 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] < {0}" ) > -1 ); |
674 | ········} |
675 | |
676 | ········[Test] |
677 | ········public void FlipParametersWithGEWorks() |
678 | ········{ |
679 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => "abc".GreaterEqual(m.Vorname) ); |
680 | ············var qs = vt.QueryString; |
681 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] <= {0}" ) > -1 ); |
682 | ········} |
683 | ········[Test] |
684 | ········public void FlipParametersWithLTWorks() |
685 | ········{ |
686 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => "abc".LowerThan(m.Vorname) ); |
687 | ············var qs = vt.QueryString; |
688 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] > {0}" ) > -1 ); |
689 | ········} |
690 | ········[Test] |
691 | ········public void FlipParametersWithLEWorks() |
692 | ········{ |
693 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => "abc".LowerEqual(m.Vorname) ); |
694 | ············var qs = vt.QueryString; |
695 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] >= {0}" ) > -1 ); |
696 | ········} |
697 | |
698 | ········[Test] |
699 | ········public void GTWorks() |
700 | ········{ |
701 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.GreaterThan( "abc" ) ); |
702 | ············var qs = vt.QueryString; |
703 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] > {0}" ) > -1 ); |
704 | ········} |
705 | |
706 | ········/// <summary> |
707 | ········/// This class provides Signatures for Server Functions |
708 | ········/// </summary> |
709 | ········class ServerFunctions |
710 | ········{ |
711 | ············public static string TestFunction(string name, int val) |
712 | ············{ |
713 | ················return null; |
714 | ············} |
715 | |
716 | ············public static int ParameterlessFunction() |
717 | ············{ |
718 | ················return 0; |
719 | ············} |
720 | |
721 | ············[ServerFunction("JSON_VALUE")] |
722 | ············public static int JsonValueAsInt(string json, string path) |
723 | ············{ |
724 | ················return 0; |
725 | ············} |
726 | |
727 | ············[ServerFunction( "JSON_VALUE" )] |
728 | ············public static string JsonValueAsString( string json, string path ) |
729 | ············{ |
730 | ················return null; |
731 | ············} |
732 | |
733 | ········} |
734 | |
735 | ········[Test] |
736 | ········public void CanCallServerFunctions() |
737 | ········{ |
738 | ············var vt = pm.Objects<Reise>().Where( m => m.Zweck.GreaterThan( ServerFunctions.TestFunction( m.Zweck, 42 ) ) ); |
739 | ············var qs = vt.QueryString; |
740 | ············Assert.AreEqual( qs, $"SELECT {this.reiseFields} FROM [Reise] WHERE [Reise].[Zweck] > TestFunction([Reise].[Zweck], {{0}})" ); |
741 | ············vt = pm.Objects<Reise>().Where( m => ServerFunctions.ParameterlessFunction() > 2143 ); |
742 | ············qs = vt.QueryString; |
743 | ············Assert.AreEqual( qs, $"SELECT {this.reiseFields} FROM [Reise] WHERE ParameterlessFunction() > {{0}}" ); |
744 | ············// Assume for a moment, that Reise.Zweck contains a Json string. |
745 | ············vt = pm.Objects<Reise>().Where( m => ServerFunctions.JsonValueAsInt(m.Zweck, "$.original.id") > 2143 ); |
746 | ············qs = vt.QueryString; |
747 | ············Assert.AreEqual( qs, $"SELECT {this.reiseFields} FROM [Reise] WHERE JSON_VALUE([Reise].[Zweck], {{0}}) > {{1}}" ); |
748 | ············vt = pm.Objects<Reise>().Where( m => ServerFunctions.JsonValueAsString( m.Zweck, "$.original.someString" ) == "Hello" ); |
749 | ············qs = vt.QueryString; |
750 | ············Assert.AreEqual( qs, $"SELECT {this.reiseFields} FROM [Reise] WHERE JSON_VALUE([Reise].[Zweck], {{0}}) = {{1}}" ); |
751 | ············vt = pm.Objects<Reise>().Where( m => ServerFunctions.JsonValueAsString( m.Zweck, "$.original.someString" ).Like("Hello") ); |
752 | ············qs = vt.QueryString; |
753 | ············Assert.AreEqual( qs, $"SELECT {this.reiseFields} FROM [Reise] WHERE JSON_VALUE([Reise].[Zweck], {{0}}) LIKE {{1}}" ); |
754 | ········} |
755 | |
756 | |
757 | ········[Test] |
758 | ········public void GEWorks() |
759 | ········{ |
760 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.GreaterEqual( "abc" ) ); |
761 | ············var qs = vt.QueryString; |
762 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] >= {0}" ) > -1 ); |
763 | ········} |
764 | ········[Test] |
765 | ········public void LTWorks() |
766 | ········{ |
767 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.LowerThan( "abc" ) ); |
768 | ············var qs = vt.QueryString; |
769 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] < {0}" ) > -1 ); |
770 | ········} |
771 | ········[Test] |
772 | ········public void LEWorks() |
773 | ········{ |
774 | ············var vt = pm.Objects<Mitarbeiter>().Where( m => m.Vorname.LowerEqual("abc") ); |
775 | ············var qs = vt.QueryString; |
776 | ············Assert.That( qs.IndexOf( "[Mitarbeiter].[Vorname] <= {0}" ) > -1 ); |
777 | ········} |
778 | |
779 | ········[Test] |
780 | ········public void GTWithByteArrayWorks() |
781 | ········{ |
782 | ············var arr = new byte[] { 1, 2, 3 }; |
783 | ············var vt = pm.Objects<DataContainer>().Where( m => m.ByteArrVar.GreaterThan( arr ) ); |
784 | ············var qs = vt.QueryString; |
785 | ············Assert.That( qs.IndexOf( "[DataContainer].[ByteArrVar] > {0}" ) > -1 ); |
786 | ········} |
787 | |
788 | ········[Test] |
789 | ········public void GEWithByteArrayWorks() |
790 | ········{ |
791 | ············var arr = new byte[] { 1, 2, 3 }; |
792 | ············var vt = pm.Objects<DataContainer>().Where( m => m.ByteArrVar.GreaterEqual( arr ) ); |
793 | ············var qs = vt.QueryString; |
794 | ············Assert.That( qs.IndexOf( "[DataContainer].[ByteArrVar] >= {0}" ) > -1 ); |
795 | ········} |
796 | ········[Test] |
797 | ········public void LTWithByteArrayWorks() |
798 | ········{ |
799 | ············var arr = new byte[] { 1, 2, 3 }; |
800 | ············var vt = pm.Objects<DataContainer>().Where( m => m.ByteArrVar.LowerThan( arr ) ); |
801 | ············var qs = vt.QueryString; |
802 | ············Assert.That( qs.IndexOf( "[DataContainer].[ByteArrVar] < {0}" ) > -1 ); |
803 | ········} |
804 | ········[Test] |
805 | ········public void LEWithByteArrayWorks() |
806 | ········{ |
807 | ············var arr = new byte[] { 1, 2, 3 }; |
808 | ············var vt = pm.Objects<DataContainer>().Where( m => m.ByteArrVar.LowerEqual( arr ) ); |
809 | ············var qs = vt.QueryString; |
810 | ············Assert.That( qs.IndexOf( "[DataContainer].[ByteArrVar] <= {0}" ) > -1 ); |
811 | ········} |
812 | |
813 | ········[Test] |
814 | ········public void GTDummyImplementationWithByteArrayWorks() |
815 | ········{ |
816 | ············var arr1 = new byte[] { 1, 2, 3, 4 }; |
817 | ············var arr2 = new byte[] { 1, 2, 3 }; |
818 | ············Assert.That( arr1.GreaterThan( arr2 ) ); |
819 | ············arr1 = new byte[] { 1, 3, 1 }; |
820 | ············arr2 = new byte[] { 1, 2, 3 }; |
821 | ············Assert.That( arr1.GreaterThan( arr2 ) ); |
822 | ············arr1 = new byte[] { 1, 2, 3 }; |
823 | ············arr2 = new byte[] { 1, 2, 3 }; |
824 | ············Assert.False( arr1.GreaterThan( arr2 ) ); |
825 | ········} |
826 | |
827 | ········[Test] |
828 | ········public void GEDummyImplementationWithByteArrayWorks() |
829 | ········{ |
830 | ············var arr1 = new byte[] { 1, 2, 3, 4 }; |
831 | ············var arr2 = new byte[] { 1, 2, 3 }; |
832 | ············Assert.That( arr1.GreaterEqual( arr2 ) ); |
833 | ············arr1 = new byte[] { 1, 3, 1 }; |
834 | ············arr2 = new byte[] { 1, 2, 3 }; |
835 | ············Assert.That( arr1.GreaterEqual( arr2 ) ); |
836 | ············arr1 = new byte[] { 1, 2, 3 }; |
837 | ············arr2 = new byte[] { 1, 2, 3 }; |
838 | ············Assert.That( arr1.GreaterEqual( arr2 ) ); |
839 | ········} |
840 | |
841 | ········[Test] |
842 | ········public void LTDummyImplementationWithByteArrayWorks() |
843 | ········{ |
844 | ············var arr1 = new byte[] { 1, 2, 3 }; |
845 | ············var arr2 = new byte[] { 1, 2, 3, 4 }; |
846 | ············Assert.That( arr1.LowerThan( arr2 ) ); |
847 | ············arr1 = new byte[] { 1, 2, 3 }; |
848 | ············arr2 = new byte[] { 1, 3, 1 }; |
849 | ············Assert.That( arr1.LowerThan( arr2 ) ); |
850 | ············arr1 = new byte[] { 1, 2, 3 }; |
851 | ············arr2 = new byte[] { 1, 2, 3 }; |
852 | ············Assert.False( arr1.LowerThan( arr2 ) ); |
853 | ········} |
854 | ········[Test] |
855 | ········public void LEDummyImplementationWithByteArrayWorks() |
856 | ········{ |
857 | ············var arr1 = new byte[] { 1, 2, 3 }; |
858 | ············var arr2 = new byte[] { 1, 2, 3, 4 }; |
859 | ············Assert.That( arr1.LowerEqual( arr2 ) ); |
860 | ············arr1 = new byte[] { 1, 2, 3 }; |
861 | ············arr2 = new byte[] { 1, 3, 1 }; |
862 | ············Assert.That( arr1.LowerEqual( arr2 ) ); |
863 | ············arr1 = new byte[] { 1, 2, 3 }; |
864 | ············arr2 = new byte[] { 1, 2, 3 }; |
865 | ············Assert.That( arr1.LowerEqual( arr2 ) ); |
866 | ········} |
867 | ····} |
868 | } |
869 |