How do I find the Last item in a DataTable using LINQ

Question

0.00/5 (No votes)

See more:

I read an entire database consisting of only two into a DataSet.

Using just one table from the DataSet, in this case tblPCNames. I want to write a LINQ query that will return the last ID number in the PCID field.

This is now it is done in SQL on an MS-Access 2010 database:

SQL

SELECT Last(tblPCNames.PCID) AS LastOfPCID
FROM tblPCNames;

Is this possible using the DataSet(DataTables) in memory or will I have to query the data source(database) directly.

I have searched the Internet for Visual Studio 2008 examples, but have not been able to find them. One site said "Element" calls like "Last" are not supported in LINQ. If they are correct doesn't it kinda defeat the purpose of storing data inside a DataSet and using LINQ?

Thanks,
MRM256

Posted 8-Jan-14 11:25am

Quecumber256

Add a Solution

Comments

Maciej Los 8-Jan-14 17:31pm

Does PCID is numeric value?
If yes, then use MAX()!

Quecumber256 8-Jan-14 17:52pm

Yes, it is a long integer. At first I wanted to use an AutoNumber field, but updating the DataSet inside memory and them transferring that information back to the database proved to be more trouble than me incrementing the ID number myself.

Maciej Los 8-Jan-14 18:11pm

No. It's easiest to update database (in case of autonumber field), than increamenting it yourself.
Is it sigle user or multi user database?

Quecumber256 8-Jan-14 18:55pm

When the tables are loaded into the data set both are empty. If I add an item to the PC data table it should auto increment to one, but when I look at the information stored in the data set the row returned in 0 PC item. Instead of 1 PC item. Table 2 is related to table 1 using the PCID field. So if there is no ID in table1 in the data set how can I relate it to table 2's PCID field unless I generate them myself.

Maciej Los 9-Jan-14 2:08am

Please, read my comment (related to the answer).

2 solutions

Solution 2

I found most of this code on the Internet. I tweaked it to this little function.

VB

Public Function GetNextPriKeyID(ByVal dt As DataTable) As Int32
        'Purpose:       For manually generated primary keys.
        '               Where the primary key is the first
        '               column in the table. 
        'Parameters:    dt As DataTable. 
        'Returns:       New Primary Key ID

        'Define & return maximum Primary key in table
        Dim maxNum As Int32 = (From n In dt _
                Select n.Field(Of Int32)(0)).Max()
        'Debug.Print("The max number in Primary Key " & maxNum & ".")
        'Add one to maxNum to make new unique ID
        maxNum += 1
        'Return the new Primary Key
        Return maxNum
    End Function

Posted 9-Jan-14 13:41pm

Quecumber256

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2014-01-08T11:51:00

Solution 1

Last[^] is supported by Linq query and returns the last element of a sequence.
First and Last Functions[^] repsectively return field value from the first or last record in the result set returned by a query.

But i'm not sure you want to achieve that. Probably you want to get MAX(PCID) value. Please, read my comment to the question.

Posted 8-Jan-14 11:51am

Maciej Los

v2

Comments

Quecumber256 8-Jan-14 18:08pm

If I'm following this right. I want to find the last number used in the PCID field. In this case the PCID field is calculated based on the last one in the table. For example: 1 - Item1, 2 - Item2 and so on. If I use the Max() function it would return 2; I add one to that to get the next ID number in the sequence 3.

Maciej Los 8-Jan-14 18:14pm

Sorry, but i don't get you ;( If PCID is autonumber field, you'll not be able to update it.

Quecumber256 8-Jan-14 19:59pm

In the beginning PCID was an auto number field, but if both tables are empty then you can't establish relationships between them. For some reason when you add some data into a row that contains an auto number field and save it to the data table contained in the data set the auto number field inside the data set is not incremented. The auto number field will contain a zero. The second column will contain the item I entered. Hence incrementing the ID keys manually.

Maciej Los 9-Jan-14 2:03am

It doesn't matter they are initially empty. It's matter how do you want to save data into database. Have a look here: How to: Insert New Records into a Database[^]. There is few ways...
Independently of above, datatable object has several columns. It is possible to set AutoIncrement Property[^] for DataColumn object, which means you don't need to set it manually.

BTW: Saving Data in Datasets[^]

Maciej Los 9-Jan-14 2:20am

Another useful link: Walkthrough: Saving Data from Related Data Tables (Hierarchical Update)[^]