VB Dataset Linq With String Where Clause?

Mar 17, 2009

Right now our queries can be driven through the DataTable Select statement. However, utimately we want to end up with more complex queries. We think Linq is the answer, but can't find anyexamples of this.Here is the sysnopsis:I have a dataset containingdatatable(s) of informationI have a datatable containing business rules as strings like:

(ISNULL(MEMLNAME, '') = '')
(#1880/01/01# <= CONVERT(BIRTHDATE, 'System.DateTime') AND CONVERT(BIRTHDATE, 'System.DateTime') <= #2009/12/31#)

[code].....

View 3 Replies


ADVERTISEMENT

Linq To DataSet Order By Clause

Feb 7, 2012

I have following code that creates Linq query.
I've never used Linq until today and having problem with "Order By Clause"[code..]

If I run the code, I get following error.Name 'p' is either not declared or not in the current scope. How come p!Weight in "Select Clause" works but not in "Order By Clause"?

View 1 Replies

.Net Linq - How To Append A Where Clause

Apr 23, 2009

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 - user may pass in "ABC" or "ABC DEF" or "ABC DEF GHI".The final query would be (syntax not correct i know)Select * from Documents Where Name Like %ABC% AND Name Like %DEF% AND Name like %GHI%

So, I though I could do something like this.


Dim query = From document In _context.Documents
<< loop based on number of strings passed in >>
query = query.Where( ... what goes here?? )

For some reason, being brain dead or something, I can't figure out how to make this work in VB.Net, or if I'm doing it correctly.

View 5 Replies

Where Clause On This LINQ Query

Nov 25, 2009

Dim dataContext As New ACSLostFoundEntities()

Dim query = From s In dataContext.FosterForms() _
Join c In dataContext.Fosters() _
On c.License Equals s.License _[code]....

At the end of this I want to do a Where WantedPets.Contains(criteria), is this possible?

View 1 Replies

Asp.net - Make A Dynamic Where Clause: Linq To Sql?

Jun 28, 2011

I have the following linq query:

Dim q = From definition In definitionList _
Where definition.ValueDefName.Contains(criteria)[code]....

I have already looked into this answer: LINQ - dynamic WHERE clause?Unfortunately, it doesn't work for me using .net 4.0. When I attempt to pass the criteria string in it ask for a predicate. The end goal is to add any of these:

definition.ValueDefname.Contains(criteria)
definition.ValueDefDesc.Contains(criteria)
definition.Aliaslist.Contains(Criteria)
definition.StrategicInitiative.Contains(Criteria)

to be passed into the query depending on what checkboxes the user has selected. How can I create a dynamic where clause in linq to sql? Is there new syntax for passing in a where clause as a string?

View 2 Replies

C# - Linq Query With Dynamic Where Clause?

Apr 13, 2012

I'm need to do the equivalent of a t-sql statement like this using LINQ:

SELECT * FROM mytable WHERE idnumber IN('1', '2', '3')

so in LINQ I have:

Dim _Values as String = "1, 2, 3"
Dim _Query = (From m In mytable Where idnumber = _Values Select m).ToList()

Not sure what to do with _Values to make idnumber evaluate each value in the string.

View 2 Replies

Iif Condition In Linq Query Where Clause

Aug 25, 2011

Whats wrong with the below query, I am getting this error: Nullable object must have a value.

[Code]...

View 3 Replies

LINQ To SQL And Join Two Tables With OR Clause

Apr 23, 2009

Let's say I have Plans and Documents
Dim myPlans = _context.Plans.Where(predicate1)
Dim myDocuments = _context.Documents.Where(predicate2)

I have structured the where clause for each using PredicateBuilder. So, myPlans and myDocuments have a correct SQL statement. What I'd like to do is join these two tables into one linq statement. The problem I'm having is that, by default the AND condition is joining the where clauses.

myPlans Where clause : (p.name like "%test%" AND p.name like "%bed%") OR (p.description like "%test%" AND p.description like "%bed%")
myDocuments Where clause : (d.name like "%test%" AND d.name like "%bed%") OR (d.description like "%test%" AND d.description like "%bed%")

When I combine the two the desired result Where clause is:
Where (d.ID = p.ID) AND
(myplans where clause above) OR (mydocument where clause above). Meaning, I'd like the two where clauses in each of the tables to be "or" instead of "And".

The current result where clause is:
Where (d.ID = p.ID) AND
(myplans where clause above) AND (mydocument where clause above). Meaning, I'd like the two where clauses in each of the tables to be "or" instead of "And".

I'm forming the statement like this:
Dim test = From d in myDocuments _
Join p in MyPlans on d.ID Equals p.ID _
Select d.Name, p.Name

View 2 Replies

VS 2010 LINQ To XML Customizable WHERE Clause?

Jun 13, 2012

So I have a query I'm doign where, at the moment, the places to search are set in stone, but the values that maybe be searched for is variable.Right now, I have a drop down menu that allows you to choose a character class. This limits the items to a specific class. Then I can filter on text from a textbox.

However, each item in the list has a source, based on the book it is published in. I want a user to be able to choose several from a checkbox menu, then pass those values for inclusion in the criteria for finding items.

The problem I'm having is finding the most concise way to do dynamic WHERE clauses. There are several examples out there, and even a couple don't seem to want to do exactly what I'm doing, and I'm having trouble determining if it will work for me.

View 8 Replies

.net - Reset Index In LINQ Select Clause?

Mar 13, 2012

My LINQ query:

Dim groupedData = (From p In pData _
Group By p.TruncParam Into Group) _
.SelectMany(Function(g) g.Group) _
.Select(Function(d, idx) New With { _

[Code]...

When the TruncParam changes, I would like the index (idx) in the Select clause to reset to 1. So, in the list above, the Index should be SOLUB0001, SOLUB0002, INSOL0001, INSOL0002...CLRES0001, SUMCA0001, SUME0001.

How should I alter the LINQ query?

View 1 Replies

Asp.net - Linq Where Clause Not Producing Expected Results

Nov 8, 2011

I have the following xml:

<Root>
<Result img="1.png" name="a">
<Programs>

[Code].....

What is wrong with linq query. Why does a 1 work but a 2 does not? I would also like xml structure preserved after filtering.

View 1 Replies

Can't Use A Where Clause On Both Tables In A LINQ Group Join?

Jun 7, 2011

Here's what I want to do:

Dim queryX = From m In db.Master
Where m.Field = value
Group Join d In db.Detail
On m.Id Equals d.MasterId Into Group
Where d.Field = value

In English, I want to join the master and detail tables, specifying conditions on each. But this causes the following compiler error:

"Name 'd' is either not declared or not in the current scope."

It works if I put this same condition in functional form:

Group Join d In db.Detail.Where(Function(x) x.Field = value)

but I think this is more typing, harder to understand, and introduces that irritating dummy variable. I really would prefer to use the query comprehension syntax. Is there a way to accomplish this?

View 1 Replies

Compare Nullable Types In WHERE Clause Of Linq To Sql .NET?

May 4, 2012

I am trying to query for records where that column IS NULL:

Dim UnassignedSubSvc =
From c In CurrentContext.SubService
Where c.Product.ProductSubServiceId **is null**
Select c).ToList()

View 1 Replies

Syntax For Order By Clause In Linq To Sql That Has Select New With

May 25, 2011

I have a LINQ to sql statement that joins 2 tables. I would like to add a order by clause on one of the columns. However the order by clause does not seem to take effect at all. right syntax in VB.net to achieve order by in the following:

Dim query = From dtIt In dbsomecontext.mytable
Join dtIl In dbsomecontext.anothertable On dtIt.ItemID Equals dtIl.ItemID
Where dtIl.IsAvailable = True
Order By dtIt.manufacturer

[Code]...

View 2 Replies

VS 2008 IfThen Statement In Linq To Sql Where Clause?

Aug 19, 2010

i want to use a if, then statement in a where clause in linq to sql. Is this possible? I'm trying to achieve the following:

[Code]...

I'm trying to only add the And If if intLink_pk > -1. If it = -1, then i don't want the and clause at all. Is this posssible within the linq to sql itself?I understand i can write 2 entirely different selects, but if I can't get this done, I may have to write about 50 of them as I want to use multiple Ands if certain conditions are met on the form, such as chk boxes etc.

View 3 Replies

.net - Substitute Field Name In Linq Where Clause By Combo Text?

Jun 22, 2012

I have a combo with 6 text NA,PO,FA,GO,VG,EX and I want to do a Linq query upon the selected value in this combo used as field in the where clause. for example:

'if "NA" is selected :
Dim query = from t in db.table where t.NA > 0 Select t
'If "PO" is selected :
Dim query = from t in db.table where t.PO > 0 select t

How to do this in elegant way without writing 6 if statement or using "Select case"

View 1 Replies

.net - Linq-to-Entities: LEFT OUTER JOIN With WHERE Clause And Projection?

Oct 18, 2010

how to translate a simple SQL LEFT OUTER JOIN with a two condition where clause into a working Linq-to-Entities query. There are only two tables. I need values for all rows from Table1, regardless of matches in Table2, but the WHERE clause uses fields from Table2. In SQL, the two parameters would be Table2WhereColumn1 and Table2WhereColumn2, and the query (which works) looks like this:

SELECT t1.Table1Id,
t1.FieldDescription,
t2.FieldValue

[code].....

I've tried using Group Join with DefaultIfEmpty(), as well as an implicit join (without the actual Join keyword), and I only get rows for items that have values in Table2. I'm sure this won't help, but here's an example of the Linq I've been trying that doesn't work:

Public Shared Function GetProfilePreferencesForCedent(ByVal dc As EntityContext, _
ByVal where1 As Int32, _
ByVal where2 As Int32) _

[code].....

View 1 Replies

.net - Search The Collection Of A Collection In LINQ Where Clause?

Apr 15, 2009

I've got the following ADO.NET Entity Framework Entity Data Model:I want to find all the Policyholders with both a Service of a given Id and also a Keyword of a given Status.

This LINQ Does Not Work:

Dim ServicesId As Integer = ...
Dim KeywordStatus As Integer = ...
Dim FoundPolicyholders = From p As Policyholder In db.PolicyholderSet.Include("Keywords").Include("Services") _
Where p.Services.Id = ServicesId _
And p.Keywords.Status = KeywordStatus _
Select p

The Where clause cannot search the p.Services and p.Keywords EntityCollections in that way.

[Code]...

View 1 Replies

Search The Collection Of A Collection In LINQ Where Clause?

Apr 15, 2011

search the collection of a collection in my LINQ Where clause?

View 11 Replies

Asp.net - Using Linq On A Dataset

May 18, 2010

I have a dataset that is populated via a SQL Stored Procedure, the format of which is below: Country | Brand | Variant | 2004 | 2005 | 2006 | 2007 | 2008 The number of rows varies between 50 and several thousand. What I'm trying to do is use Linq to interrogate the dataset (there will be several Linq queries based on user options), but a simple example would be to SUM the year columns based on Brand. I have the following that I believe creates a template for me to work with: But from here on I'm absolutely stuck!

[Code]...

View 1 Replies

.net - GroupBy In LINQ To DataSet?

Jul 14, 2011

Background: I'm importing data from a MySQL database into a SQL-Server database(for reports and later a SSAS-Cube). I want to normalize the data at the same time. I want to group repeating Ticket_IDs to one record in a table Contact with other useful informations and leave the rawdata in the sub-table ContactDetail(with foreignkey to Contact).Hence every record in Contact has a unique Ticket_ID.

I've decided to use strong typed datasets for the import. Now i'm wondering what's the best way to detect if i've already added a Ticket_ID. I could check for it in every loop(~100000 records) but i'm assuming that there is a better/faster way.

Simplified sample-data:

Ticket_ID ID fiContact
89442226 1 1
89442226 2 1[code].....

I will have a look if this is faster than the iterating approach with a HashSet to detect if the contact was already created.

View 2 Replies

DBNull When Using LINQ To DataSet

May 20, 2010

I've got the following LINQ Statement:[code]invRecord.Pack_Num is a field of type Integer. This means that when I try to access it, if it is DBNull I get a StronglyTypedException. The above code throws this exception. If, however, I remove the "invRecord.Pack_Num = PSNum" and in its place put something like "True", the code works fine. So I guess my question is, why is that that invRecord.IsPack_NumNull() returns False when the value is in fact DBNull and what can I use as a conditional instead? I've been beating my head against the wall for a while now and I can't find a solution to this problem.

View 1 Replies

.net - How To Work With Dataset's Datarelation In Linq

Apr 4, 2009

linq querying various nested tables in a dataset? I can't find something like that on the net.

View 1 Replies

DB/Reporting :: Add LINQ To DataSet To Current App

Jun 4, 2012

I am using VS 2008, VB.NET and Windows XP r3.I am trying to use LINQ to DataSet in my app. In my test app, I can get it to work find. My test form has an OleDbDataAdapter, an OleDbConnection and a DataSet. It also has a DataGridView bound to the one table in the DataSet, a BindingSource and a Button.[code]

View 7 Replies

Getting Error With DBNull When Using LINQ To DataSet

Nov 12, 2011

I've got the following LINQ Statement:

[code]...


invRecord.Pack_Num is a field of type Integer. This means that when I try to access it, if it is DBNull I get a StronglyTypedException. The above code throws this exception. If, however, I remove the "invRecord.Pack_Num = PSNum" and in its place put something like "True", the code works fine. So I guess my question is, why is that that invRecord.IsPack_NumNull() returns False when the value is in fact DBNull and what can I use as a conditional instead? I've been beating my head against the wall for a while now and I can't find a solution to this problem.

View 7 Replies

LINQ / Select Distinct From Dataset?

Aug 20, 2010

I have a single columned datatable inside a single tabled dataset.I just want to convert this dataset to distinct rows. Here is my code, it gives compile error '.' expected.

Dim query = _
From email In ds.Tables(0) _
Select email.Field<string>("Email").Distinct()

EDIT: I changed to (Of String) and it works... BUT NOW 'query' is an ienumerable collection of characters... not a datatable... so how do I convert back easily without manually doing a loop?

View 1 Replies

LINQ Dataset LEFT JOIN?

Jun 14, 2012

i think i have not represented my question correctly here My LEFT OUTER JOIN MSDN reference is here to visualize my problem in access databaseTABLE "T1"1234567TABLE "T2"1,sunday2,monday i need output as 1,sunday2,monday34567 i need it to done through LINQso far i did is here

Dim RIGHT_table As DataTable = ds1.Tables("t1")
Dim LEFT_table As DataTable = ds2.Tables("T2")
Dim OutPut = From LEFTsource In LEFT_table.AsEnumerable(), RIGHTsource In RIGHT_table.AsEnumerable() _

[code]...

View 3 Replies

LINQ To Dataset - How To Handle DBNull

Jan 29, 2008

This is what I'm doing to select and display 3 columns of my datatable in GridView:

Dim myCustomers = From Cust In myDataset.Customers _
Select CustomerId = Cust.CustomerId, _
CustomerName = Cust.CustomerName, _
ExpiryDate = Cust.ExpiryDate
DataGridView1.DataSource = myCustomers.ToList

The expiry date has null values in many records whose expiry date is not set.On executing the query I get runtime error:System.Data.StrongTypingException "The value for column 'ExpiryDate' in table 'Customers' is DBNull."

Obviously, my LINQ query is wrong or incomplete, but I am not able to figure the way to do it correctly.

View 13 Replies

Use Linq As A Data Source Instead Of Dataset?

Apr 19, 2010

I have used a datarepeater with a dataset and it was simple to implement by dragging fields from the Data Sources window onto the DataRepeater control

I am trying to use Linq as a data source instead of dataset.

How do I bind data to textbox with Linq for Datarepeater?

View 1 Replies

Using Linq For Object Dataset Processing?

May 26, 2011

I have a collection (IList(Of Sample)) of the following class:

Public Class Sample
Public sampleNum As String
Public volume As Integer[code].....

This collection is filled from a regex that gets passed over a file.What I want to do is use Linq to generate a collection of these for each unique samplenum using the following conditions.For each samplenum: Have the highest volume where the final is greater then one

If the sample has multiple records for this volume then pick the one with the the highest final

If the previous step leaves us with no records pick the record with the highest final ignoring volume

I am extremely new to Linq and just can't get my head around this. For now I have solved this using for each's and temporary lists but I am interested in how this would be handled using pure Linq.Sample Data:

samplenum | volume | initial | final
1 | 50 | 8.47 | 6.87
1 | 300 | 8.93 | 3.15[code]......

View 2 Replies







Copyrights 2005-15 www.BigResource.com, All rights reserved