Asp.net - Convert SQL (Pseudocode) To Dymanic LINQ Query From List<Custom> Parameters?

Apr 5, 2012

GOAL: Bind nested ListViews to LINQ generated iQueryable of anonymous type. I want to use LINQ because you can use GroupBy and bind the nested ListView to the 'it' keyword.

SETUP: I have groups of sets of conditions. Each set of conditions is stored in the BillingCodes table. Each group of BillingCodes is stored in the BillingGroups table.

I have a custom object that stores the ID, Name, and NumCodes for each BillingGroup that the user has chosen.I have a collection of these objects called GroupsList that has a list of the groups that the user has chosen.

Problem 1: I can iterate through GroupsList and grab all the IDs. How do I translate the SQL 'WHERE ID IN(a string of comma delineated IDs)' for LINQ to SQL? Is that the best way to do that?

Problem 2: Once I have the list of BillingGroups I need to iterate through each group. For each group, I need to iterate through the BillingCodes. For each BillingCode I need to generate a WHERE clause that has all of the conditions in the BillingCode. I propose something like so:

for each BillingGroup in BillingGroups
for each BillingCode in BillingGroup.BillingCodes
where1 = "..."
next
next

Problem 3: Here's the part where I don't have a clue. I need to dynamically create a query in LINQ to SQL. Keep in mind that I don't know how many groups there'll be or how many codes are in each group.

There are 2 tables:

**transactions**
transaction_id
patient_id

[code]....

View 1 Replies


ADVERTISEMENT

.net - Linq Query Ignore Empty Parameters?

Apr 20, 2010

How do I get Linq to ignore any parameters that are empty? So Lastname, Firstname, etc? If I have data in all parameters it works fine...

refinedresult = From x In theresult _
Where x.<thelastname>.Value.TestPhoneElement(LastName) And _
x.<thefirstname>.Value.TestPhoneElement(FirstName) And _
x.<id>.Value.TestPhoneElement(Id) And _

[code]....

View 1 Replies

Use Custom Type In A LINQ Query?

Jun 4, 2009

If you have custom type, and you wish to assign a List(of T) to that custom type[code]...

View 1 Replies

Custom Date Format From Linq To SQL Query?

Jul 13, 2010

Im using the folowing function to return a SelectList. I need a custom format on Descr, but replacing Key.ToString() to Key.ToString("DD/MM/YY") render me the error "Method 'System.String ToString(System.String)' has no supported translation to SQL.". How could i use a custom date format on Descr?

Public Function ReturnDates(ByVal Param_Pesq_Cod As Integer) As SelectList
Dim Qry = From E In DB.Execs _
Where E.Pesq_Cod = Param_Pesq_Cod _

[code]....

View 1 Replies

How To Convert SQL Query Into LINQ

Dec 8, 2011

I am new to LINQ, how to convert the following SQL query to LINQ?
SELECT
Date,ShiftName,Max(Score) AS Score, 1 AS IsPreferred
FROM
Temp_Nurse_Fill_RequestNumbers
group by
ShiftName,date

View 4 Replies

How To Convert The SQL Query Into LINQ

Dec 8, 2011

I am new to LINQ.convert the following SQL query to LINQ?

SELECT
Date,ShiftName,Max(Score) AS Score, 1 AS IsPreferred
FROM

[code]....

View 4 Replies

Convert A List Linq Expression To Defined List Class?

May 26, 2012

y have this class

Private Class MyClass
Public Property propertyOne() as String
Public Property propertyTwo() as String

[code].....

View 2 Replies

Convert LINQ Query Expression From C# To VB?

Mar 10, 2009

I have the following C# LINQ query expression:

[code]...

how to convert this to VB.NET?

View 1 Replies

Convert SQL Query/subquery To LINQ (VB)?

Jan 16, 2012

Here is my SQL query..

SELECT EqID, MakeID, Model, Description,
(SELECT MAX(EvDateEnd) AS MaxOfDateEnd
FROM tblEvent WHERE (EqID = tblEquip.EqID) AND (Event = 'REG')) AS RegExpire,

[code]....

For example, I want to get the Max EvDateEnd from the Events table for each record in tblEquip, but not all records in tblEquip have the "REG" event, while others have multiple REG events.Some events (like EAE) I leave the tblEvent.EvDateEnd field blank to show the event is open and ongoing, such as issuing a vehicle to an employee(entity). when the vehicle is returned I enter the date in EvDateEnd essentially closing the event.

View 1 Replies

Convert A LINQ Query Resultset To A DataTable?

Jul 20, 2010

How can i write this query with LINQ to a FoxPro database?SELECT count(*) FROM Table group by item1I wrote it as below, but it doesn't work

Dim Query
Dim dt As New DataTable
Dim da = New Odbc.OdbcDataAdapter("SELECT * FROM table1",connection)

[code].....

View 3 Replies

Convert This String-based Sql Query To Use Linq?

Feb 3, 2011

I have the following routine (that works) but which is messy to update owing to the hand-typed strings it uses:

Private Sub ListDefaults()
Dim conn As New SqlConnection( _
"server=bas047AUTODESKVAULT;Database=DWGDetails;Integrated Security=SSPI")
'Dim conn As New SqlConnection( _

[code]....

View 1 Replies

Use LINQ Query To Convert DBNull To String?

Oct 12, 2009

I've created a small WPF app in VB9 to catalog our sizable DVD collection. This exercise has been more about learn programming than the need to have a movie database app, but I digress.The SQL database I created has a field for "Genre" that does not have an entry for all the movies in our collection. As such, when I run the method that filters the movie listing by "Genre" the compiler throws the "StrongTypingException was unhandled" error stating "The value for column 'Genre' in table 'Movies' is DBNull.".

I now understand where the DBNull problem comes from and have been searching for several hours to find a VB solution that converts the DBNull fields to strings on the fly during the LINQ query. I've found many solutions people have tried but, since I'm quite new to all this, none of them have worked for me. Pasted below is the method that throws the exception.

[Code]...

View 2 Replies

VS 2008 Convert A String Into A Linq Query?

May 20, 2012

Is there a way to convert a string into a linq query? Searching for those words brings up tons of stuff unrelated to what I'm trying to do..For instance, this works, of course...

Dim lstMyQuery = (From item in htMyTable.values where item(0) = "Apple" Select item)

But somehow I want to do this:

Dim strQuery = "From item in htMyTable.values where item(0) = " & CHR(34) & "Apples" & CHR(34) & " Select item"
Dim lstMyQuery = (Somehow run/convert strQuery as a query)

The reason is strQuery will be different each time depending on what the user has selected earlier in the program.R if lstMyQuery is already a collection, is there a way to add to it?

Dim lstMyQuery = (From item in htMyTable.values where item(0) = "Apples" Select item)

(this obviously doesn't work, but it's basically what I want to do...)

lstMyQuery.Add(From item in htMyTable.values where item(0) = "Oranges" Select item)

View 3 Replies

.net - Linq Query To List?

Sep 2, 2011

Dim query = From o In myContainer.MyObjects Select o.MyStringProperty Distinct
Dim myProperties As List(Of String) = query.ToList????? 'no way!!!'
"query" type is IEnumerable(Of String)

I tried to use the query directly as a DataSource of a (infragistic) combobox, but it throws me NullReferenceException, so I decided to convert it to a listof strings, to be a more "classical" datasource.

Dim values As List(Of String) = query.AsQueryable().ToList()
does not work either: Value of type 'System.Collections.Generic.List(Of System.Linq.IQueryable(Of String))' cannot be converted to 'System.Collections.Generic.List(Of String)'.

View 3 Replies

Vb.net - Convert From Custom List To List Of String

Mar 25, 2010

I have the following code:

[code]...

The intention is to convert an IList of custom objects to a string equivalent comprising each element in the Ilist. Unfortunately I can't seem to find a way to get the underlying data of the custom object, and of course as in the above example, using object simply gives me a string of types definitions, rather than access to the underlying data.

View 2 Replies

Copy Linq Query To List?

Sep 12, 2011

'Read RESX file and returns a List(Of ResXDataNode)[code]...

Provide two separate copies of the node? Or will the List and Linq query still be referencing the same data?

View 2 Replies

LINQ Query For Deleting One Column From List(Of)

Jun 28, 2011

I have this code:

[Code]...

View 3 Replies

Convert A Two-table Exists Query From SQL To Linq Using Dynamic Fields In The Subquery?

Jun 7, 2011

I'm trying to query old Access database tables and compare them with SQL Server tables.They often don't have primary keys, or they have extra fields that had some purpose in the nineties, etc., or the new tables have new fields, etc.I need to find records - based on a set of fields specified at runtime - that are in one table but not another.So, I do this kind of query all the time in SQL, when I'm comparing data in different tables:

dim fields_i_care_about as string = "field1, field2, field3"
'This kind of thing gets set by a caller, can be any number of fields, depends on the
'table
dim s as string= ""

[code]....

It tells me it can't convert a Boolean - Is there any way to do this without Linq expressions? They seem far more complex than what I'm trying to do here, and they take a lot of code, and also I can't seem to find examples of Expressions where we're comparing two fields in a subquery.Is there a simpler way? I know I could do the usual EXISTS query using JOIN or IN - in this case I don't need the query to be super fast or anything. And I don't need to use a DataTable or DataSet - I can put the data in some other kind of object.

View 1 Replies

VS 2010 Custom Query Against List<string>

Aug 15, 2011

I have a List<string> that contains paths, such as:

[Code]...

Given a given root, such as Root, I would like to "query" this list to return only the first level values, such as:

[Code]...

View 2 Replies

Asp.net - Linq To Sql Convert To Generic List In .NET?

Jul 2, 2009

I am trying to learn some VB.NET for my coo-op that starts next week, and for that reason i took my portfolio web site that is on C# and just started to converting it to VB.NET to get familiar with the syntax.

I am sure my problem is simple however i have a hard time solving it. I am trying to grab data with the linq query and then return it as list to bind to the Repeater.I have the following function:

[Code]...

And it would work fine. However if I try to use ToList() in VB.NET i am getting error that it cannot convert it from ling to sql List to the generic list. How do i do this convertion in VB.NET ? or maybe there is another way ?

I am decent when it comes to C# however somewhat lost with the VB.NET syntax.

View 1 Replies

.net - LINQ Query Returns List Of Empty Objects?

May 3, 2012

I have a query that 'selects' object of a type:

Dim l as IList(Of Foo) = (From dataRow As DataRow In table.Select()
Where CStr(dataRow("Column1")) = "A"
Select New Foo(CStr(dataRow("Column1")), _
CStr(dataRow("Column2")))).ToList()

What's happening is that if i set a break-point to the constructor of Foo and step, the constructor is hit and the parameters are loaded with the arguments. However, l has empty Foo objects (the members in every object are Nothing). What could be happening here?

View 1 Replies

Assign Result (from LINQ To XML Query) To List(Of String)?

Jul 6, 2011

Is it wrong? Please suggest me correct way.

Public Function ReadXML() As List(Of String)
Dim list As New List(Of String)
Dim xmlDoc As XDocument = XDocument.Load("C:\MappingFile.xml")

[code].....

View 1 Replies

Linq Query To Compare If Collection Contains List Of String?

Feb 3, 2012

I am using a path comparison to find any children tags which works well when there is only 1 tag selected. This is my code for when multiples tags are selected but it is not working. Can you point me in the right direction?

Single Tag Selection (working)
Dim tagpath = uxTags.SelectedItem.Text
lnqCases = From i In lnqCases Where i.HelpDeskTagItems.Any(Function(x)

[code]...

View 1 Replies

Linq To Xml Query Returning A List Of All Child Elements?

Mar 8, 2010

I have got an xml document which looks something like this.

<Root>
<Info>
</Info>
<Info>

[Code]...

How can i write a linqToXML query so that it returns me an IEnumerable containing each child element, in this case all five child elements of , so that i could iterate over them. The order of child elements is not definite, neither is number of times the may appear.

View 1 Replies

VS 2008 Get Original List Index Of LINQ Query

Oct 24, 2010

If I use a LINQ query over some type of collection, is there any way to know the initial index of an element of the result?

Purpose: I make a query to set as source of a DataGridView, and on a (button)cellclick event I want to a call a method that needs to reference the collection at the correct index.

View 13 Replies

.net - LINQ Query For Filter By Selected Items In Checkbox List?

Jun 30, 2011

Could not find this through Google or in SO questions.I have a checkbox listbox on my form. I want to filter my List by the list of selected Ids from that listbox that are checked, in SQL I would have done this like "Where TypeId In (1, 4, 5, 7)"... how do I do that in LINQ?

I feel like I am missing a really obvious answer, but cannot get it.

For argument sake... here is the what I have for sample data:

In Colors (List<of currentColors>)
ID, Name, TypeId
1, Red, 1
2, Blue, 1
3, Green, 2
4, Pink, 3

Selected Types 2 and 3 in CheckboxList: filteredColors

[Code]...

View 1 Replies

.net - Convert Resultant Values To List Of Strings In Linq?

Oct 1, 2010

I'm having issues with returning a list of strings of the .Value of a Linq query:

Dim details = <Details>
<Vector size="5">
<Item>Syntactic Structures</Item>
<Item>Introduction</Item>
<Item>The Independence of Grammar</Item>

[Code]...

Is correct in that it returns the list of XElements I want (items 1 - 4, base 0), but I really just need a list of strings for the .Value of those XElements. Maybe I'm just dense here, but anything I've tried in the chapterTitles query isn't working (appending with .ToList.ToString, etc.). details.<Vector>.<Item>.Skip(1).Take(4).Value just returns the first XElement's value.

View 1 Replies

Linq To Object: ToList Can Not Convert To Generic List

May 8, 2009

Dim tenItem = From t In _InvalidFeeList _
From i In ItemCount _
Where t.FeeCode <> i.FeeCode _

[Code]....

I am getting "Can not convert to System.generic.list" error. I look all over for this error and it seem like my code should work for example here

edit: I think i should explain what I'm trying to do here. I want to compare two object list and select the object from the InvalidFeeList if the FeeCode is not equal to the object in ItemCount FeeCode and take the first 10 objects from the InvalidFeeList.

View 2 Replies

Pass A Parameter From The Select List Into A Function For Joining A Linq Query?

May 25, 2010

I have a query that can be summarised in SQL as follows;

Select
S.StockCode
From
StockToCheck As S

[Code]....

Where the S var is a list of strings. This gives the error 'S' is not declared and points to the S in the function call / join (GetPOSStock). So it does not seem possible to do this in Linq, can anyone confirm?

View 1 Replies

LINQ Query To Group Data Between Two List Collections, Populating A Nested Object

Oct 26, 2011

I have these objects:[code]Using LINQ I need to take a List(Of MakeInfo) and a List(Of ModelInfo) and aggregate the StockInfo from ModelInfo into the List(Of MakeInfo).So for each MakeInfo I will have a total count of all stock where MakeInfo.Name = ModelInfo.Make, and also a minimum price.I think it's going to be something like this, but I'm having trouble accessing the nested object and not sure if it's going to be possible with a single query.[code]

View 2 Replies







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