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