After reading it, this is not a duplicate of Explicit vs Implicit SQL Joins. The answer may be related (or even the same) but the question is different. What is the difference and what should go in each? If I understand the theory correctly, the query optimizer should be able to use both interchangeably. ...
In another posting: Does Linq-To-Sql support composable queries there was discussion on how to compose/concat where clauses dynamically. This appears to be done with an "AND" (i.e. the first where clause and the second where clause are joined by an AND). What I am wondering is if there is a way to compose Linq queries with an OR. Exam...
Hi! I've got a mySql stored procedure that looks like this-- delimiter | create procedure GetEmployeeById(in ID varchar(45)) begin select id, firstName, lastName, phone, address1, address2, city, state, zip, username, password, emptypeid from myschema.tblemployees t ...
Simply put, I have a table with, among other things, a column for timestamps. I want to get the row with the most recent (i.e. greatest value) timestamp. Currently I'm doing this: SELECT * FROM table ORDER BY timestamp DESC LIMIT 1 But I'd much rather do something like this: SELECT * FROM table WHERE timestamp=max(timestamp) Howeve...
I read about using the CASE expression inside the WHERE clause here: http://scottelkin.com/sql/using-a-case-statement-in-a-sql-where-clause/ I'm trying to use this to filter results from my select statement, based on a contract number which will be passed in by the user's application. My code currently throws an error of 'Invalid param...
Hi, I have some code like this: Function GetTypeFromTableName(ByVal _TableName As String, ByVal _DataContext As DataContext) Dim Mytype As Type = (From t In _DataContext.Mapping.GetTables Where t.TableName = "dbo." + _TableName Select t.RowType.Type).SingleOrDefault Return Mytype End Function Dim DBA As New LINQDataContext _...
I was going through a few queries I am maintaining, and a programmer had put in the queries "where 1=1" to me that always seems to evaluate to true. Are there benefits to this? Duplicate: Why would someone use WHERE 1=1 AND in a SQL clause? That question isn't an answer to this question. Where-clause: select * from table where 1...
Hi, Is there a way I can improve this kind of SQL query performance: INSERT INTO ... WHERE NOT EXISTS(Validation...) The problem is when I have many data in my table (like million of rows), the execution of the WHERE NOT EXISTS clause if very slow. I have to do this verification because I can't insert duplicated data. I use SQLServ...
Hi, I have a course table which I need to search based on keywords typed in the search box. Here is a sample query: SELECT * FROM Courses WHERE Title LIKE '%word%' OR Title LIKE '%excel%' OR Contents LIKE '%word%' OR Contents LIKE '%excel%' How can I convert this in LINQ where LINQ would dynamically generate WHERE statements based...
I've just replaced this piece of code: foreach( var source in m_sources ) { if( !source.IsExhausted ) { .... } } with this one: foreach( var source in m_sources.Where( src => !src.IsExhausted ) ) { ... } Now the code looks better (to me) but I'm wondering what's really happening here. I'm concerned about perf...
With a LINQ-TO-SQL linq query - does the SQL generated respect the 'Where conditions' order? For instance: int[] result = (from r in DB.Accounts where r.AccountID > 10 && r.Name == "Harry").ToArray(); If there are thousands of rows, and there is an Index on the Name column, will the SQL query by the Indexed column fi...
I need Help for dynamic where clause over relational tables (one to many) in LinqToSql. User select conditions from page. (there is 4 input that user select the clauses) For example CompanyName and CompanyTitle from Customer table and OrderDate and ShipCity From Order table. But user can select one ore many of them from page interfa...
Pretty new to VB.Net. Having a bit of trouble here with something I though should be simple. Keeping it simple. Let's say i have a Document table with "Name" that I want to search on (in reality there are several other tables, joins, etc ..). I need to be able to build the query where clause based on string values passed in. Example - ...
I have this small SQL query. SELECT a.`id` , a.`title` , a.`date` , ( SELECT MAX( grade ) FROM tests WHERE userid = 41 AND presid = a.`id` ) AS grade FROM `presentations` a WHERE a.`visible` = 1 AND `grade` >= 5 ORDER BY `grade` DESC This gives me the error 1054 - Unknown column 'grade' in 'where clause' But if...
What is the best way to assemble a dynamic WHERE clause to a LINQ statement? I have several dozen checkboxes on a form and am passing them back as: Dictionary<string, List<string>> (Dictionary<fieldName,List<values>>) to my LINQ query. public IOrderedQueryable<ProductDetail> GetProductList(string productGroupName, string productTypeNa...
If I need to search on Date of birth which is stored without the hours and minutes but the date I have to search with includes hours and minutes what is the best way to return all rows where date is matched on only day, month and year i.e. Stored as 01-JAN-50 10.22.06.000000000 date selected 01-JAN-50 10.22.06.010101120 If I us...
I'm trying to make something like this: int count = new Select().From(tblSchema).Where("Type & 1").IsEqualTo("1").GetRecordCount(); And the error message is: Incorrect syntax near '&'. Must declare the scalar variable "@Deleted". Is it possible to do that with SubSonic? ...
Hello, In my program, we store a user's IP address in a record. When we display a list of records to a user, we don't want to give away the other user's IP, so we SHA1 hash it. Then, when the user clicks on a record, it goes to a URL like this: http://www.example.com/allrecordsbyipaddress.php?ipaddress=SHA1HASHOFTHEIPADDRESS Now, I ne...
I have an UPDATE statement that's intended to update a status field for a limited number of records. Here's the statement: UPDATE warehouse_box SET warehouse_box_status_id = wbsv.warehouse_box_status_id FROM warehouse_box_status_vw wbsv INNER JOIN pallet_warehouse_box pwb ON wbsv.warehouse_box_id = pwb.warehouse_box_id INNER JOIN rou...
I need to make a SELECT with a call of a stored procedure in the WHERE clause. It should be something like that.... SELECT distinct top 10 i.x, d.droit FROM v_droit d, v_info i WHERE d.nomdroit='yy' AND i.id<>2 AND (select val from (exec up_droits(i.x, d.droit)) <>3 But it does not work... Any idea? Don't say to replace t...