How to get value through linq in c#

Question

0.00/5 (No votes)

See more:

I have one datatable

Column0           |   Column1   | Column2
04/05/2015 00:10  |   xyz       |  ON
04/05/2015 00:20  |   xyz       |  OFF
04/05/2015 00:30  |   abc       |  ON
04/05/2015 00:40  |   abc       |  OFF

I want this

DateTime         |  SrNo  |  Ontime   | Off Time  |  RunningTime
04/05/2015 00:10 |    1   |    00:10  |   00:20   |    00:10 //(off time) - (on time)

i m using this linq query

C#

var query = MyDataTable.AsEnumerable()
           .Where(r => r.Field<string>("Column0").Contains("04/05/2015"))
           .Where(r => r.Field<string>("Column1").Contains("xyz"))
           //.Select(r => DateTime.Parse(r.Field<string>("Column0")).ToString("dd/MM/yyyy HH:mm:ss"));
           .Select((r, iterator) => new
           {               
               DDate = DateTime.Parse(r.Field<string>("Column0")).ToString("dd/MM/yyyy HH:mm:ss"),
               SrNo = iterator + 1,
               On = r.Field<string>("Column2").Contains("ON"),
               Off = r.Field<string>("Column2").Contains("OFF")
               Running = r.Field<string>("Column2")
           });

How to get correct result

Help me

Posted 18-Jun-15 6:19am

Manamohan Jha

Updated 18-Jun-15 6:30am

Andy Lanng

v2

Add a Solution

Comments

Richard Deeming 18-Jun-15 13:14pm

Don't store dates as strings! Use one of the available date / time types[^] instead.

Manamohan Jha 19-Jun-15 5:50am

i am not storing dates as string, i am getting all data from CSV file

public DataTable ReadCSV(string filepath)
{
DataTable dtDataSource = new DataTable();
string[] fileContent = File.ReadAllLines(filepath);
if (fileContent.Length > 0)
{
string[] columns = fileContent[0].TrimEnd(',').Split(',');
for (int i = 0; i < columns.Length; i++)
{
dtDataSource.Columns.Add("Column" + i);
}
//
for (int i = 0; i < fileContent.Length; i++)
{
string[] rowData = fileContent[i].TrimEnd(',').Split(',');
dtDataSource.Rows.Add(rowData);
}
}
return dtDataSource;
}

2 solutions

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)

Mathi Mani · Answer 1 · 2015-06-18T07:31:00

In order to achieve the result you are looking for, you have to group the records based on Column1 and get the result from that.

Here is the LINQ query to get the result you are looking for but not exactly in the same format you asked for. This query requires some minor changes to get the result in the format you need. I am leaving it for you to try.

C#

int iteration = 0;
var result = from dr in MyDataTable.AsEnumerable()
             group dr by dr.Field<string>("column1") into grouped
    select new
    {
        Name = grouped.Key,
        SrNo = ++iteration,
        OnTime = (from dr1 in grouped
                  select dr1.Field<string>("column0")).Min(),
        OffTime = (from dr1 in grouped
                  select dr1.Field<string>("column0")).Max(),
        RunTime = Convert.ToDateTime((from dr1 in grouped
                   select dr1.Field<string>("column0")).Max()) -
                   Convert.ToDateTime((from dr1 in grouped
                    select dr1.Field<string>("column0")).Min())

    };

Maciej Los · Answer 2 · 2015-06-18T08:38:00

Check this:

C#

DataTable one = new DataTable();
one.Columns.Add(new DataColumn("Column0", Type.GetType("System.DateTime")));
one.Columns.Add(new DataColumn("Column1", Type.GetType("System.String")));
one.Columns.Add(new DataColumn("Column2", Type.GetType("System.String")));
one.Rows.Add(new Object[]{new DateTime(2015, 5, 4, 0, 10, 0), "xyz", "ON"});
one.Rows.Add(new Object[]{new DateTime(2015, 5, 4, 0, 20, 0), "xyz", "OFF"});
one.Rows.Add(new Object[]{new DateTime(2015, 5, 4, 0, 30, 0), "abc", "ON"});
one.Rows.Add(new Object[]{new DateTime(2015, 5, 4, 0, 40, 0), "abc", "OFF"});


var qry = one.AsEnumerable()
        .GroupBy(r=>new{DDate = r.Field<DateTime>("Column0").ToString(@"yyyy/MM/dd"), Id=r.Field<string>("Column1")})
        .Select((g, i)=>new
            {
                Id = g.Key.Id,
                SrNo = i+=1,
                DDate = g.Key.DDate,
                OnTime = g.Min(c=>c.Field<DateTime>("Column0")).ToString(@"HH:mm:ss"),
                OfTime = g.Max(c=>c.Field<DateTime>("Column0")).ToString(@"HH:mm:ss"),
                RunningTime = (g.Max(c=>c.Field<DateTime>("Column0")) - g.Min(c=>c.Field<DateTime>("Column0"))).TotalMinutes
            });

Result:

Id    SrNo    DDate        OnTime     OfTime     RunningTime
xyz   1       2015-05-04   00:10:00   00:20:00   10 
abc   2       2015-05-04   00:30:00   00:40:00   10

How to get value through linq in c#

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0