>>Hi everybody,
>>
>>I am contemplating on a better way to implement the following.
>>
>>I have a wide table called max4sale which has type and department, category, item, lesson_cat, etc. columns. They are populated depending on the type. The structure of this table is pre-defined and we're not going to change it (just to avoid discussion about the table's design).
>>
>>In VFP I have the following code to check for possible conflicts when a row is inserted / updated in that table:
>>
>>

>>wait window "Checking for time conflicts..." nowait noclear
>>		text TO lcSQL TEXTMERGE noshow
>>        select COUNT(ID) as conflicts
>>        from dbo.max4sale where type = <<VFP2SQL(type)>>
>>        and <<thisform.cWhere>>
>>        AND Start_Time >= <<VFP2SQL(Start_Time)>>
>>	    and Start_time <= <<VFP2SQL(End_Time)>>
>>	    and ID <> <<VFP2SQL(Id)>>
>>		ENDTEXT
>>
>>		*    _cliptext = m.lcSQL
>>
>>		mysqlexec(m.lcSQL, 'csrTemp', program())

>>
>>
>>where thisform.cWhere was constructed in form's Init depending on the type.
>>
>>So, I'm thinking of somehow re-producing that either as LINQ query or as a stored procedure where I'd pass many parameters and use dynamic SQL to construct the query.
>>
>>Assuming I am going to try the first approach of the LINQ query, how can I do that type based condition dynamically, e.g.
>>
>>if type = 1, then I want to check department column, for example. If type = 2, then department and category, etc.
>>
>>
>>Thanks in advance.
>
>

>// Set the initial query to find overlapping times. 
>var query = dbContext.max4sale.Where(m4s => m4s.Start_Time < this.End_Time && m4s.End_Time > this.Start_Time && m4s.ID != this.ID);
>
>// Add additional filters based on the type
>switch(type)
>{
>    case 1:
>        query = query.Where(m4s => m4s.Type == 1);
>        break;
>    case 2:
>        query = query.Where(m4s => m4s.Type == 1 || m4s.Type == 2);
>        break;
>}
>
>// Get the count of conflicts
>var conflicts = query.Count(); // Or .Any() if you just need to know if there is conflicts.
>

>
>You could also implement this as a validation attribute (https://msdn.microsoft.com/en-us/library/system.componentmodel.dataannotations.validationattribute%28v=vs.110%29.aspx). See http://blogs.microsoft.co.il/shimmy/2012/01/23/uniqueattribute-that-validates-a-unique-field-against-its-fellow-rows-in-the-database-inherits-dataannotationsvalidationattribute/ for an example of a validation attribute that compares against other records.

Thanks a lot, Rob, I hoped you'll jump in. I think I'll go with that direct solution and just throw a response, not implement as a validation attribute.

BTW, this blog is a blog of my virtual friend Shimmy Weitzhandler :) He is a violinist but also a programmer...