As it is known, not every method can be translated into SQL via LINQ-to-SQL. These methods or properties being met in expressions causes exception at runtime - 'The member 'PropertyName' has no supported translation to SQL' Evidently, these properties must be calculated on server-side. But is there any approach to do it automatically? ...
how do I implement something like this in linq. In my linq code it gets the best answers where the answer userID does not equal the Question userid. This is suppose to filter users picking their own post as best answer. If the user picked their own answer as best answer then it must be upvoted at least 3 times. var AwardedAnswers = from...
I have a simple DB that has Sites, and each Site has a bunch of Posts. I'm trying to get all the "Public" Posts of a certain Site (I have a variable called site that is already an instance brought by EF) The first obvious thing is: var posts = from post in site.Posts where post.Public == true orderby post...
I'm using a CTE to generate a range of dates. 12/02/2010 10:00:00 12/02/2010 10:59:59 12/02/2010 11:00:00 12/02/2010 11:59:59 12/02/2010 12:00:00 12/02/2010 12:59:59 I then left join this to a indexed view containing huge amounts of date. I have 2 options for counting between the date ranges 1) i would SUM(case) test the log_date t...
I am trying to query this very complicated XML document using xDocument and LINQ to XML. I want to perform the following action: Get all elements that answer to a certain criteria, and if they don't, return another attribute from the xDocument. Example: <cars> <car> <patrol type="oil"> <url> http://Toyotaoil.co...
Hello All, How can I do the following in SQL Server DECLARE @Local nvarchar(20) SET @Local = 'True' SELECT * FROM My_Table WHERE my_ID IN (IF @Local = 'True' SELECT AllIDs FROM ATable ELSE SELECT TeamIDs FROM TeamTable ) ...
The normal ASP.NET TableAdapters are good for simple where clauses, for example - "where city = @city and state = @state and zip = @zip" But how do I design a DAL that allows me to use any combination of the parameters - "search only by city" or "search by zip and state" or "search by city and state".....or even more complex "search by ...
I have an Entity which has an association to another Entity annotated with @Where, like so public class EntityA { @OneToMany @Where(...) private List<EntityB> entityBList; } Recently the inevitable has happened, I need to load EntityB's that don't conform to the @Where clause. I could remove the @Where annotation, but it...
I'm using SqlServer 2005 and I have a column that I named. The query is something like: SELECT id, CASE WHEN <snip extensive column definition> END AS myAlias FROM myTable WHERE myAlias IS NOT NULL However, this gives me the error: "Invalid column name 'myAlias'." Is there a way to get around this? In the past I've included the...
I have a query where I have a complex date expression as one of the columns. SELECT date_column + INTERVAL( complex_jimmy_jam ) DAY AS complex_date_calculation FROM table I want to refer to that column in the where clause WHERE complex_date_calculation < NOW() But mysql chokes on it. 1054: Unknown column 'complex_date_calcul...
I'm having trouble crafting a fairly simple query with Doctrine... I have two arrays ($countries, $cities) and I need to check whether database record values would match any inside either. I'm looking for something like: ->whereIn('country', 'city', $countries, $cities) ... with 'country' being a WHERE IN for $countries and 'city' be...
This doesn't work: INSERT INTO users (username, password) VALUES ("Jack","123") WHERE id='1'; Any ideas how to narrow insertion to a particular row by id? ...
I would like to use something like the following: SELECT city FROM cities WHERE city LIKE %D% AND country_id = '12' ...
I have the following MySQL query: SELECT * FROM customer WHERE fName LIKE '%#attributes.q#%' AND deleted = 'N' OR lName LIKE '%#attributes.q#%' AND deleted = 'N' This works fine for guesses, but if you provide an exact match: "Bill Clinton" fname=bill lname = clinton to the query above you get 0 results? where bill clint gives...
I'm trying to check if a date is null in linq and if it isn't check its a future date. QuestionnaireRepo.FindAll(q => !q.ExpiredDate.HasValue || q.ExpiredDate >DateTime.Now).OrderByDescending(order => order.CreatedDate); I need the second check to only apply if the first is true. I am using a single repository pattern and FindAll acce...
Hi, I've got the following type of SQL: UPDATE photo AS f LEFT JOIN car AS c ON f.car_id=c.car_id SET f.photo_status=1 , c.photo_count=c.photo_count+1 WHERE f.photo_id IN ($ids) Basically, two tables (car & photo) are related. The list in $ids contains unique photo ids, such as (34, 87, 98, 12). With the query,...
I have a basic group by/avg statement: select url, avg(contentping+tcpping), count(*) from websites ws, ping pi where ws.idwebsite = pi.idwebsite and errortype is null group by url order by avg(contentping+tcpping) asc; What I want to do now is to drop any results which have a higher than average ping of 500. How can I do this...? ...
Hi! This query that I have is returning therapists whose 'therapistTable.activated' is equal to false as well as those set to true! so it's basically selecting all of the db, any advice would be appreciated! ` $query = "SELECT therapistTable.* FROM therapistTable ...
hi, i have a varchar field with the content like these: a,b,c,d e,d,a,c b,q,d,e i need to do a query that select only the rows with the field that has elements equals with an input string. ex input: c,a rows selected: a,b,c,d e,d,a,c is possible without use the OR (field like '%a%' OR field like '%c%') ? thanks ...
If you create a Filter object that contains criteria for Linq that normally goes in a where clause like this: var myFilterObject = FilterFactory.GetBlank(); myFilterObject.AddCondition("Salary", "lessThan", "40000"); var myResult = myRepository.GetEmployees(myFilterObject); How would you match the Linq field to the Field Name with...