LINQ Query

577

Solutions

418

Contributors

Language Integrated Query (LINQ) is a Microsoft .NET Framework component that adds native data querying capabilities to .NET languages, although ports exist for Java, PHP, JavaScript and ActionScript. LINQ extends the language by the addition of query expressions, which are akin to SQL statements, and can be used to conveniently extract and process data from arrays, enumerable classes, XML documents, relational databases, and third-party data sources.

Share tech news, updates, or what's on your mind.

Sign up to Post

I have the below Linq query to retrieve a provider record with the related primary specialty.

I need to modify this to also include a secondary specialty which is coming out of the same DB table, but with a flag isSecondary set and assign the values to two new properties on the ProviderDetail object.

I'm thinking this is another .SelectMany but having trouble making it work.


DBContext.Provider
			.GroupJoin(DBContext.ProviderSpecialties, p => p.providerId, ps => ps.providerId, (x, y) => new { Provider = x, ProviderSpecialties = y })
			.Where(w => w.Provider.providerId == providerId)
			.SelectMany(x => x.ProviderSpecialties.Where(w => w.isPrimary == true)
			.DefaultIfEmpty(), (x, y) => new { Provider = x.Provider, ProviderSpecialties = y })
			.Select(m => new ProviderDetail
			{
				providerId = m.Provider.providerId,
				licenseNumber = m.Provider.licenseNumber,
				licenseExpiration = m.Provider.licenseExpiration,
				licenseStateId = m.Provider.licenseState,
				licenseStateName = m.Provider.State.abbreviation,
				firstName = m.Provider.firstName,
				lastName = m.Provider.lastName,
				title = m.Provider.Title1.name,
				primarySpecialtyName = m.ProviderSpecialties.Specialty.epicName,
				primarySpecialtyId = m.ProviderSpecialties.specialtyId == null ? 0 : m.ProviderSpecialties.specialtyId
                                //secondarySpecialtyName = ???,
                                //secondarySpecialtyId = ???
			}).FirstOrDefault();

Open in new window

0
Cloud Class® Course: CompTIA Cloud+
LVL 12
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Hi Experts,

I have a query:

var TonerTotal = (from r in _recyclingRepository.Table
                  where r.Created < date && r.Approved != null &&
                  Constants.TonerIds.Contains(r.RecyclingPackagingId)
                  select r.Quantity).Sum();

Open in new window


When the query returns null it goes to error.  How can I handle it? When the query returns null I want TonetTotal=0 otherwise TonetTotal should be the value returned.

Thanks in advance.
0
Hi All,
Advance thanks!
i am using vs2017, c#
Am using List of objects[10]. have to pick object 9 from list and insert in between 1 and 2..

List<TestClass> list = new List<TestClass>()
{
    new TestClass() { FirstColumn="1",SecondColumn = "2", ThirdColumn = "3"  },
    new TestClass() { FirstColumn="q",SecondColumn = "w", ThirdColumn = "e"  },
    new TestClass() { FirstColumn="a",SecondColumn = "s", ThirdColumn = "d"  },
    new TestClass() { FirstColumn="1",SecondColumn = "s7", ThirdColumn = "d"  },
    new TestClass() { FirstColumn="2",SecondColumn = "s6", ThirdColumn = "d"  },
    new TestClass() { FirstColumn="3",SecondColumn = "s5", ThirdColumn = "d"  },
    new TestClass() { FirstColumn="4",SecondColumn = "s4", ThirdColumn = "d"  },
    new TestClass() { FirstColumn="5",SecondColumn = "s3", ThirdColumn = "d"  },
    new TestClass() { FirstColumn="6",SecondColumn = "s2", ThirdColumn = "d"  },
    new TestClass() { FirstColumn="7",SecondColumn = "s1", ThirdColumn = "d"  },
};

Open in new window


now how to insert  FirstColumn="6" in beween 3 and 4
like this

List<TestClass> list = new List<TestClass>()
{
    new TestClass() { FirstColumn="1",SecondColumn = "2", ThirdColumn = "3"  },
    new TestClass() { FirstColumn="q",SecondColumn = "w", ThirdColumn = "e"  },
    new TestClass() { FirstColumn="a",SecondColumn = "s", ThirdColumn = "d"  },
    new TestClass() { FirstColumn="1",SecondColumn = "s7", ThirdColumn = "d"  },
    new TestClass() { FirstColumn="2",SecondColumn = "s6", ThirdColumn = "d"  },
    new TestClass() { FirstColumn="3",SecondColumn = "s5", ThirdColumn = "d"  },
    new TestClass() { FirstColumn="6",SecondColumn = "s2", ThirdColumn = "d"  },
    new TestClass() { FirstColumn="4",SecondColumn = "s4", ThirdColumn = "d"  },
    new TestClass() { FirstColumn="5",SecondColumn = "s3", ThirdColumn = "d"  },
    new TestClass() { FirstColumn="7",SecondColumn = "s1", ThirdColumn = "d"  },
};

Open in new window


Kind regards,
Pooja
0
How do I find the matches and delta's between two lists?
public class ComponentsSQLModel
{
    public string PartNoSQL { get; set; }
}

public class ComponentsExcelModel
{
    public string PartNoExcel { get; set; }
}

List<ComponentsSQLModel> sqlModel = new List<ComponentsSQLModel>();
List<ComponentsExcelModel> excelModel = new List<ComponentsExcelModel>();

Open in new window


Want to know the matches and the delta's
0
Hi,

I have a dictionary:
Dictionary<int, int> dict = new Dictionary<int, int>();

Open in new window

I want to get the key(s) that has the max value.
I used it as follow:

var max = dict.FirstOrDefault(x=>x.Value==maxValue).Key;

Open in new window


But this will return the first key only.
For example, if
maxValue=5 

Open in new window

and associated keys are 1 and 4, I want to print out both 1 and 4.

How can I accomplish this?
0
I have created a LINQ to return data from SQL server in JSON format which contains nested values.
Attach the code and output, the JSON is showing duplicate values for FieldID:6

var query =  from g in entity.ConfigSearchFieldGroupings
                         join f in entity.ConfigSearchFieldGroupingFields
                         on g.GroupingID equals f.GroupingID
                         join d in entity.ConfigDataFields
                         on f.FieldID equals d.FieldID
                         join ss in entity.ConfigSelections
                         on d.FieldID equals ss.FieldID into t
                         from rt in t.DefaultIfEmpty()
                         join s in entity.ConfigSourceSystems
                         on d.SystemCode equals s.SystemCode
                         where g.IsActive == true
                         && d.IsActive == true
                         && s.IsActive == true
                         && g.GroupingID == siGroupingID
                         && d.IsSearchable == true
                         select new
                         {
                             g.GroupingName,
                             f.FieldID,
                             d.FieldName,
                             d.FieldText,
                             d.FieldDescription,
                             d.FieldCategory,
                             d.FieldDataType,
                             d.FieldLength,
                             

Open in new window

0
I want to updated the LeadTime field in ComponentModel from LeadTimeModel where the PartNo's match.  They are both List<T>.
    public class ComponentsModel
    {
        public string PartNo { get; set; }
        public string Cost { get; set; }
        public string LeadTime { get; set; }
    }

Open in new window

    public class LeadTimeModel
    {
        public string PartNo { get; set; }
        public long LeadTime { get; set; }
    }

Open in new window

0
I am maintainig a sort order based on a table and its current records and one of the types in the table.  If there are no records I  want a one back.  if there are I want the SortOrder value +1 back

This TSQL with sub query works well in TSQL

SELECT
    ISNULL(NextSortOrder, 1) AS NextSortOrder
FROM
(
    SELECT
        MAX(SortOrder) + 1 AS NextSortOrder
    FROM
         PDFFilesMultiple
    WHERE(PDFPageId =@PDFPageId)
) AS a;

Trying in LINQ with

                    using (var context = new BookEntities())
                    {

                        var query = (from SortTable in (from PDFFilesMultiple in
                             (from PDFFilesMultiple in context.PDFFilesMultiple
                              where
                                    PDFFilesMultiple.PDFPageId == pdfPage
                              select new
                              {
                                  PDFFilesMultiple.SortOrder,
                                  Dummy = "x"
                              })
                                    group PDFFilesMultiple by new { PDFFilesMultiple.Dummy } into g
                                    select new
                                    {
                                        NextSortOrder = (g.Max(p => p.SortOrder) + 1)
                                    })
                            select new
                            {
                                nso = …
0
I am writing an app that will get a list of directories and files.  I am populating a List<string> to store this information.

I created an ENUM to determine what directories and file extensions should be excluded.  I was trying to figure out how to write linq to "removeall" from the list against the values in the ENUM.  

Want to see if there is a way without looping through each value in List<string>.
public static class Enumerators
{
	public enum ExcludedDirectories
	{
		ABC
	}

	public enum ExcludedFiles
	{
		docx,
		Thumbs
	}
}

Open in new window

My solution is below.
    public static class EnumValidation
    {
        public static List<string> DeleteValues<T>(List<string> value)
        {
            var deletionvalues = Enum.GetValues(typeof(T));

            foreach (var del in deletionvalues)
            {
                value.RemoveAll(x => x.Contains(del.ToString()));
            }

            return value;
        }
    }

Open in new window

Is there a more efficient way?
0
I need some help for a Filter By Date with LINQ query.

So,

This is my Class:

public class MyList
    {
        public string Ent { get; set; }
        public string Con { get; set; }
        public int Count { get; set; }
        public double? Med { get; set; }
        public DateTime Data { get; set; }
    }

Open in new window


My query:

public IEnumerable<MyList> LoadData()
    {
        var ctx = new DbContext();

        var query = (from t in ctx.tblTimes.AsQueryable().Where(s => s.Included == true)
                     join av in ctx.tblPrincipal on t.AID equals av.AID
                     join c in ctx.tblCon on av.ConID equals c.ConID
                     join e in ctx.tblEnt on av.EntID equals e.EntID
                     group t by new
                     {
                         c.Name,
                         e.Entity
                     } into grp
                     select new MyList
                     {
                         Con = grp.Key.Name,
                         Ent = grp.Key.Entity,
                         Count = grp.Count(),
                         Med = grp.Average(s => s.Time),
                         Data = grp.Select(s => s.tblPrincipal.Data).FirstOrDefault()
                     });
        if (CmbCon.SelectedItem != null)
        {
            var selected = (tblCon)CmbCon.SelectedItem;
            query = query.Where(s => s.Name == selected.Name);
        }
        if (CmbEnt.SelectedItem != null)
        {
        

Open in new window

0
Introducing Cloud Class® training courses
LVL 12
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

I wrote an app that uses
List<string> str = new List<string>();

Open in new window

Problem I have is I'm adding to many items to the string which is causing issues.

I perform a search in the string
var found = (from r in str
             where r == value
             select r);

Open in new window

List<string> can be replaced with a StringBuilder.  

Can you query a StringBuilder with Linq?
0
Hi All,
Advance thanks to all.
am using C#
have 3 c# list objects (has list of items of AnonymousTaskMode)
 
public class AnonymousTaskMode
{
    public int TaskId { get; set; }
    public string TaskName { get; set; }
    public DateTime ModifiedOn { get; set; }
}

Open in new window


foreach taskid in obj1, get date value(ModifiedOn ) from obj1  and update the respective obj2.modifiedon and obj3.ModifiedOn

Kind regards,
Pooja
0
I want output like

[
 {
  filename: filename1,
  User: {

  }
 },
 {
  filename: filename2,
  User: {
  
  }
 }
]

Open in new window


Query Example

var query = (from usr in context.User
                            join fs in context.FileS on usr.ID equals fs.UserID
                            where fileName.Contains(fs.FileName)
                            select (new UserPartial
                            {
                                FirstName = usr.FirstName,
                                LastName = usr.LastName,
                                Email = usr.Email,
                            })).Select(s => new {
                                filename = fileName
                            });

Open in new window


with my LINQ query. Here only second query result I am getting. I want the output of both Select queries.
0
Hi All,
Advance thanks!
 Please find the table records (attached). Trying to get latest record using where clause....

guidCurrentTaskinstanceid is the comparison value..



var results = (from a in context.Prod_NextStages
                                       where a.CompletedPgTaskInstanceId == guidCurrentTaskinstanceid
                                       group a by a.NextStgId into g
                                      select g.OrderByDescending(t => t.ModifiedOn)).FirstOrDefault();

I am expecting the output ECF8F654-1B1C-4413-8C10-3E47B1750638 since it i the latest record...

Kind regards,
Pooja
Max-date-using-where-clause.PNG
0
How to get all unmatched records from table2 uisng LINQ

below is my code...

 protected void Button1_Click(object sender, EventArgs e)
   
    {

        DataTable dt1 = new DataTable();
        DataTable dt2 = new DataTable();
        DataTable dtMatched = new DataTable();
        DataTable dtUnMatched = new DataTable();

        dt1.Columns.Add("Sno");
        dt1.Columns.Add("name");

        DataRow workRow = dt1.NewRow();
        workRow["Sno"] = "1";
        workRow["name"] = "name1";
        dt1.Rows.Add(workRow);
        workRow = dt1.NewRow();
        workRow["Sno"] = "2";
        workRow["name"] = "name2";
        dt1.Rows.Add(workRow);
        workRow = dt1.NewRow();
        workRow["Sno"] = "3";
        workRow["name"] = "name3";
        dt1.Rows.Add(workRow);
     

        dt2.Columns.Add("Sno");
        dt2.Columns.Add("name");
        dt2.Columns.Add("Country");
        workRow = dt2.NewRow();
        workRow["Sno"] = "1";
        workRow["name"] = "name1";
        workRow["Country"] = "India1";
        dt2.Rows.Add(workRow);
        workRow = dt2.NewRow();
        workRow["Sno"] = "2";
        workRow["name"] = "name22";
        workRow["Country"] = "India2";
        dt2.Rows.Add(workRow);
        workRow = dt2.NewRow();
        workRow["Sno"] = "3";
        workRow["name"] = "name3";
        workRow["Country"] = "India3";
        dt2.Rows.Add(workRow);
        workRow = dt2.NewRow();
        workRow["Sno"]…
0
I want to remove duplicates.  Using the below example I want "Alpha" and "Gamma" removed from the list because "Alpha" has 2 A's and "Gamma" has two A's and two M's.

List<string> theList = new List<string>() { "Alpha", "Beta", "Gamma", "Delta" };

The values that should be returned should be distinct in each item.  No duplicate letters.
0
Hi ,
Advance thanks!
How to concat 2 columns in one column on the list . Am using c#

obj a = new obj();
a has ...
a.firstcol=1
a.secondcol=2
a.thirdcol=3


List<o> lsto= new List<o>();

if the lsto has 100 rows.. how to get
firstcol as  "firstcol - secondcol-thirdcol) for all the 100 rows...


what i am doing is
foreach(var i in lsto)
{
i.firstcol = i.firstcol + "-" + i.secondcol + "-" + i.thirdcol;
}

is there any easy solution....

Kind regards
Pooja
0
How do you create a LINQ statement in Visual Basic to query an AccessDataSource?   I want the results to target a datasource that will be bound to a control.

Thanks
0
Hello,

I am working on a small app using ASP.NET core 2 and razor pages.  I have EF core 2 installed and am trying to join 2 tables via linq.  

I am sure I am doing something terribly wrong.  I have 2 tables that I want to join so that I can display fields from both tables.  

No errors, but I cannot show a field from the RcMalttStatus table on my html page.  I can only call fields from the query from the RcSchedule table.

If you are able to help, please let me know if I have given enough information.  

Sincere thanks,
Bonnie

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore;
using Royal.Data;
using Royal.Data.ManDev;

namespace Royal.Quality.Pages
{
    public class JobsModel : PageModel
    {
        public readonly Royal.Data.RoyalDevContext _context;

        public JobsModel(Royal.Data.RoyalDevContext context)
        {
            _context = context;

        }

        public IList<RcSchedule> RcSchedule { get; set; }

       public IList<RcMalttStatus> RcMalttStatus { get; set; }

 
        public async Task OnGetAsync()
        {

           var query =
                from scheduleJob in _context.RcSchedule
                join status in _context.RcMalttStatus on new { scheduleJob.Factory, scheduleJob.Job } equals new { status.Factory, Job = status.JobNumber }
                where 

Open in new window

0
Keep up with what's happening at Experts Exchange!
LVL 12
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Hi,

I want to group by data rows from data table and print those rows in different Excel with the name of group by values.

below steps need to perform..

1. Main table is data table and it contains some rows.

2. i want to group by rows using department column. In that case i can get 3 different groups such that E-101,E102,E103 will be formed.

3. After grouping those values, i want to print and store in different Excel files in the name of E-101.xls, E-102.xls, E-103.xls,etc..

4. The sample output has highlighted in the image (==> E-101.xls, E-102.xls, E-103.xls )

4.  How to do this in C#.
0
I have the following query in linq
   var lastPosRaw = (from lp in db.tblCommonTrackingDatas
                                              where lp.vpkDeviceID == deviceid && lp.vReportID == 124 && lp.dGPSDateTime <= positionDateTime
                                              orderby lp.dGPSDateTime descending
                                              select new
                                              {
                                                  vLatitude = lp.vLatitude,
                                                  vLongitude = lp.vLongitude,
                                                  vTextMessage = lp.vTextMessage
                                              });

                            var lastPos = lastPosRaw.Select(a => new Last_Location
                            {

                                vLatitude = long.Parse(a.vLatitude),
                                vLongitude = long.Parse(a.vLongitude),
                                vTextMessage = a.vTextMessage

                            }).FirstOrDefault();



                            if (lastPos != null)
                            {//do stuff}

Open in new window


It seems to be taking time, and on closer inspection, it seems to be returning lots of rows from the DB then selecting the top 1
I need it to  return the first row only, however when i do this

 var lastPosRaw = (from lp in db.tblCommonTrackingDatas
                                              where lp.vpkDeviceID == deviceid && lp.vReportID == 124 && lp.dGPSDateTime <= positionDateTime
                                              orderby lp.dGPSDateTime descending
                                              select new
                                              {
                                                  vLatitude = lp.vLatitude,
                                                  vLongitude = lp.vLongitude,
                                                  vTextMessage = lp.vTextMessage
                                              }).FirstOrDefault();

Open in new window



the line below errors on the SELELCT word

var lastPos = lastPosRaw.Select(a => new Last_Location

Open in new window


'<anonymous type: string vLatitude, string vLongitude, string vTextMessage>' does not contain a definition for 'Select' and no extension method 'Select' accepting a first argument of type '<anonymous type: string vLatitude, string vLongitude, string vTextMessage>' could be found (are you missing a using directive or an assembly reference?)

Any ideas?
0
Hi All,
Advance thanks
Have C# list of objects


obj(Object definition)
Name 1  type string
Name 2 type string
Name 3 type string, etc....

List<obj> lobj=  new List<obj>();
lobj.add("test1",test2","test3");
lobj.add("test1",test1","test1");
lobj.add("test2",test1","test3");
lobj.add("test1",test2","test3");

Now i need to find duplicate Name1 field values from the list and keep the first one on the list.

Expected list
("test1",test2","test3")
("test2",test1","test3")


Kind regards,
Pooja
0
Hi,

I am using string which contains some values.

List<string> result = new List<string>();

result = "A1-B1-C1-D1-_F1";

when symbol "-_" found in the result string then it should take only or consider "_".

So the final string would be "A1-B1-C1-D1_F1"

How to replace...
0
string SpeSubject = "ENGLISH"
List<string> SubjectAll = new List<string>() { "PHYSICS=100", "MATHS=100", "ENGLISH=99", "CHEMISTRY=97", "BIOLOGY=96" };

string[] Result = SubjectAll.Where(str => SpeSubject.Any(str1 => str.Contains(str1))).ToArray();

How to fetch the Matching values from SubjectAll.

For Example, My searchstring is ==> SpeSubject = "English"

I want to search this field ("ENGLISH") in SubjectAll list and return as "ENGLISH=100"

i written the query below. But, it will fetch only the first records.

How to fetch the exact record..?



string SpeSubject = "ENGLISH"
List<string> SubjectAll = new List<string>() { "PHYSICS=100", "MATHS=100", "ENGLISH=99", "CHEMISTRY=97", "BIOLOGY=96" };

string[] Result = SubjectAll.Where(str => SpeSubject.Any(str1 => str.Contains(str1))).ToArray();

Finally i want to store English and Marks are seperate.


string ColumnName = Result.Select(str2 => str2.Split('=')[0]).FirstOrDefault();  

string ColumnValue = Result.Select(str2 => str2.Split('=')[1]).FirstOrDefault());

my final value should be as

ColumnName = ENGLISH
ColumnValue = 99

where we need to change the code...?
0
I need to know how to write a ForEach Loop to find a string value that correlates to an integer. Is this possible?

ForEach ID in StringField
  Do Something
0

LINQ Query

577

Solutions

418

Contributors

Language Integrated Query (LINQ) is a Microsoft .NET Framework component that adds native data querying capabilities to .NET languages, although ports exist for Java, PHP, JavaScript and ActionScript. LINQ extends the language by the addition of query expressions, which are akin to SQL statements, and can be used to conveniently extract and process data from arrays, enumerable classes, XML documents, relational databases, and third-party data sources.

Top Experts In
LINQ Query
<
Monthly
>