Sql - Dynamically Varied Number Of Conditions In The 'where' Statement Using LINQ?
Feb 3, 2010
I'm working on my first project using LINQ (in mvc), so there is probably something very simple that I missed. However, a day of searching and experimenting has not turned up anything that works, hence the post.
I'm trying to write a LINQ query (Linq to SQL) that will contain a multiple number of conditions in the where statement separated by an OR or an AND. We don't know how many conditions are going to be in the query until runtime. This is for a search filter control, where the user can select multiple criteria to filter by.
select * from table
where table.col = 1
OR table.col = 2
OR table.col = 7
'number of other conditions
Before I would just construct the SQL query as a string while looping over all conditions. However, it seems like there should be a nice way of doing this in LINQ. I have tried looking using expression trees, but they seem a bit over my head for the moment. Another idea was to execute a lambda function inside the where statement, like so:
For Each value In values matchingRows = matchingRows.Where(Function(row) row.col = value)However, this only works for AND conditions. How do I do ORs?
the testBoolean is set to the value True, but for some reason when debugging, it step through both conditions, so in the end the button1.visble becomes False, but I want it to be set to True.[code]....
I currently have an If...Then statement that checks if all fields of a form have been filled before saving the info in a file. I'd like to be able to tell which field has not been filled to give the user a dynamic message (at the moment, it only tells them that the checklist is not completed ) :
If (txtNoDemande.TextLength = 8 _ Or txtNoDemande.TextLength = 13 _ And txtDateLoad.TextLength <> 0 _
[CODE]...
Is there a way to determine which of these conditions is false without having to do a seperate If statement for each ?
I am trying to write a Function which will return a data table, filled with a data from a database.
The Function will accept 6 diffrenet parameters as Optional. Each one of them will be an additional condition to be added to a where clause, if it`s provided when the function is beeing called.
Is there any method in .NET to simply add a new condition to the where clause?
Select Distinct s.* from #ScopeIDs x Join Scopes s on s.ScopeID=x.ScopeID or x.ScopeID is null
in LINQ to SQL? (This query would return all Scopes whose ScopeID is present in #ScopeIDs, unless one of the entries in #ScopeIDs is null, in which case it returns all Scopes).
A "literal" translation doesn't work, since LINQ doesn't support joining with multiple conditions - code along these lines ...
From x in ScopeIDs Join s in Scopes on s.ScopeID equals x.ScopeID or x.ScopeID equals nothing Distinct Select s ... doesn't compile.
myFilteredContractors = (From c In myFilteredContractors Join cc In myConClasses On c.ContractorId Equals cc.ContractorId Where inClassifications.Contains(cc.ClassificationId)[code].....
This is properly ordering this list of contractors by the number of classifications that they have.I also want to order them by whether or not they have a field set (CompanyOverview), which if is an empty string should be below those contractors who have set their CompanyOverview. Also, after the CompanyOverview is ordered I want to order by Registration Date.So it should order by:
Number of Classifications Whether Overview has been set (c.CompanyOverview) Registration Date (c.AppliedDate)
I am doing a bar cash register and I want to use a if statement to see if fosters button and number 1 on the number pad are both pressed then multiply constant by quantity number
I want to put it (the select count statement) in this LINQ statement so I can get the sales count in my linq statement: Dim TheLeads = (From L In DB.Leads Where L.IsDeleted = False Select L).ToList() Is this possible to do in LINQ?
This may be simple, but im having a tough go at it. I'm trying to make a "review" section in a step through form, where the user can see everything that has been entered and if they choose go back and "edit" that information again. One section only has to show the information that has been added by the user, basically if they don't select a certain checkbox there is no reason to display the info for that box.I want to to dynamically create labels in a Panel (Panel7) on a TabPage (TabPage3), only for the checkbox information that was selected. This is what I have right now, and I am unable to see anything on the page, I have also tried without the panel and straight onto the tabpage itself to no avail.
MsgBox(CurPat, MsgBoxStyle.Critical) If (CurPat = "True") Then Dim lblTemp As New Label
In my application I use Linq to SQL to communicate with my SQL database.To setup a connection I added a Linq to SQL class, selected a database and choosen a SQL table.After this I use the following code for showing SQL data:Using aw As New LinqtoSQLDataContext
Dim Data = From c In aw.TestDatabase_TestTable _ Select c.No, c.Description DataGridView1.DataSource = Data End Using
Now the link to the SQL database is hardcoded and the databasename must be used in every Linq-statement.Is it possible to make the link to the SQL database dynamic so I can switch easyily between a test and production database?Can the tablename inside the Linq statements be dynamic too?
I want to filter records dynamically on user choice. The user may choose to show the debit amount greater than credit or credit amount greater than debit. Accordingly I want to put a condition similar to either totDebit>totCredit orTotCredit>totDebit`
Dim query = _ From product In Bills.AsEnumerable() _ Group product By accode = product.Field(Of String)("ACCODE"), _
I have 290 Group Policy Backup xml files which I need to enumerate in separate folders.
With each Group Policy backup xml file, I need to query the Policy settings.
Anyone who's looked at a Group Policy xml backup file before would know they're chock-a-block full of Namespace declarations.
I want to know, using Linq to XML, as I query each xml file, how can I dynamically query the XML the Namespace and then append the Namespace into the Linq query for the child nodes/values?[code]...
I need to know how to make a number to number if statement e.g. if min = 20 to 40 then ...
Current code:
If hour = 8 And min = 20 or 21 or 22 or 23 or 24 or 25 or 26 or 27 or 28 or 29 or 30 or 31 or 32 or 33 or 34 or 35 or 36 or 37 or 38 or 39 or 40 Then TextBox1.Text = "Test" Else TextBox1.Text = "Text1" End If
I need to build a dynamic linq query with or operators. I have seen PredicateBuilder but that is in C# and my project is in VB. Basically I need to build a WHERE clause similar to this:
Where((this = 1 AND that = 2) OR (this = 1 AND that = 4) OR (this = 2 AND that = 4))
but the problem is the number will have to be determined dynamically at runtime, and added using a loop, like
for each item in myItems query = query.OR (this = item.a AND this = item.b) next
Ok im trying to do a if statement in Linq and was wondering if it was possible to do something like:
Dim loadFriends = From p In db.UserRelationships Where p.aspnet_User.UserName = User.Identity.Name _ Or p.aspnet_User1.UserName = User.Identity.Name And p.Type = 1 _ Select New With {if p.aspnet_user1.user = "a certan username" then .username = _ p.aspnet_user.username else .username = p.aspnet_user1.Username}
I'm relatively new with LINQ, but I'm going to be getting into it a lot more. Is the following a practical application of LINQ, or is there a better way to do something like this? [code] I was thinking if I could get the property name somehow as an object I could just do one LINQ statement,
I have a Customer object which has a collection of ContactNumbers. Is it possible with LINQ to get a list of Customers where one of the contact numbers = '123'?
Public Class Customer Public Overridable Property ContactNumbers As List(Of ContactNumber) End Class
I'm developing an application to allow engineers to conduct simple single table/view queries against our databases by selecting Database, Table, Fields.
I get how to use the Dynamic LINQ Library Sample to provide for dynamically selecting the Select, Where and Order by Clauses at runtime but I'm at an impass on how to allot for table choice.
Is there a way to provide for dynamically selecting the "from" table at run time
EDIT
So Both of the answers seem to be saying the same general Idea. I'm going to try to convert the C# into VB and get it to work.
The first answer converts to
NotInheritable Class DataContextExtensions Private Sub New() End Sub
I only want to return a certain number of rows into the DataTable via LINQ's Take or use it on the Rows property, but I am not sure how or where to do it: Here is the current code:
Dim dt As DataTable = GetDataTable("sp", params) For Each dr As DataRow In dt.Rows Dim o As New OR()
I have something that I want to do and what looks very simple, but I cannot get it to work. I want to build a function that gets as input a xdocument and some other strings. It should have a return that contains a filtered part of the xdocument in a way that I can use it as input for a new Linq function. This is what I mean:
Private Function fncGetFilteredDecendantsOfNode(ByVal xdocDoc As XDocument, _
I have the following code:[code]When I run it, looking at the debugger everything looks okay but when I check the database table it is supposed to be inserting into - the column values (for occupantnum) haven't changed - they are all still null! Not sure if this is related, but note that I've now updated j to explicitly be an integer. I noticed while debugging that wResident.occupantnum was appearing as Type "Integer?" - but this doesn't seem to have made any difference - when I rerun it it still says Type "Integer?"
Dim gg = From gs In AllData_TableList Order By gs.Biology Descending, gs.ChemistryDescending, gs.English Descending, gs.Frensh DescendingAll I need is to make this Statement order Dynamically like this
Dim Variable1 as string=Biology Dim Variable2 as string=Chemistry Dim Variable3 as string=English
We use LINQ to SQL extensively, and one of the biggest performance pitfalls we've run into is situations where a query can't be converted to SQL, and so an entire database table gets loaded into memory and the query performed by .NET. For example, this query
Dim Foo = (From c in Db.Contacts Select c Where c.ContactID=MyContactID) translates to a simple SQL query, whereas Dim Foo = (From c in Db.Contacts Select c Where c.ContactID=SafeInt(MyContactID))
doesn't because SafeInt is our own function and doesn't have a SQL equivalent; so the whole contacts table gets loaded into memory and searched by LINQ to objects, which is vastly slower. With a little experience, we've gotten better at avoiding this kind of situation. But it's not always obvious, and this is the sort of problem that's often not revealed until you're working with a lot of data and things start to slow down. Is there a straightforward way to see - whether in Visual Studio or some other way - how LINQ queries will be handled, other than trial and error?
