LINQ Query

578

Solutions

419

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: Microsoft Office 2010
LVL 12
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

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 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
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
Cloud Class® Course: Python 3 Fundamentals
LVL 12
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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
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
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
Cloud Class® Course: Amazon Web Services - Basic
LVL 12
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

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
Need help in C# 5 replacing value with empty collection for the null ones

    

//Structure of the class Info
public class Info
{
     public IList<string> Type { get; set; }
     public IList<string> Flow { get; set; }
}

//Trying to convert the returned result into dictionary
db.InsertMissingInfo(result.Item1.ToDictionary(k => k.Key, v => new Info { Type = v.Value.Type, Flow = v.Value.Flow  }));

Open in new window


Here result.Item1 is Tuple which i am converting in to dictionary
How can i check if the v.Value.Flow is null here if it is null i want to put an empty collection
0
So lets say I have this XML document:

<s:Envelope 
        xmlns:s="http://www.w3.org/2003/05/soap-envelope" 
        xmlns:u="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-utility-1.0.xsd">
        <s:Header>
            <VsDebuggerCausalityData 
                xmlns="http://schemas.microsoft.com/vstudio/diagnostics/servicemodelsink">uIDPo4tYpt6X40FEk+VSAe5mc8MAAAAAP497cBuXfk+uFIOY80O0iuLtIW56q7hLktgVYPhbnHMACQAA
            </VsDebuggerCausalityData>
            <o:Security s:mustUnderstand="1" 
                xmlns:o="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-wssecurity-secext-1.0.xsd">
                <o:BinarySecurityToken u:Id="uuid-10490fb0-8ee0-4a4c-a8db-77242c9a3b7f-2" ValueType="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-x509-token-profile-1.0#X509v3" EncodingType="http://docs.oasis-open.org/wss/2004/01/oasis-200401-wss-soap-message-security-1.0#Base64Binary">MIIF+TCCBOGgAwIBAgIQIWv3OdE866kXP/....t</o:BinarySecurityToken>
                <e:EncryptedKey Id="_0" 
                    xmlns:e="http://www.w3.org/2001/04/xmlenc#">
                    <e:EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#rsa-oaep-mgf1p">
                        <DigestMethod Algorithm="http://www.w3.org/2000/09/xmldsig#sha1" 
                            xmlns="http://www.w3.org/2000/09/xmldsig#" />
                    </e:EncryptionMethod>
                    <KeyInfo 
                        

Open in new window

0
Hello... new to linq lambda - what is wrong with my query? - I get 'sequence contains no elements' error when there are no providerSpecialties records for the given provider.

This should behave like a left join from the provider entity.

            
             return DBContext.Provider.GroupJoin(DBContext.ProviderSpecialties,
                    p => p.providerId, ps => ps.providerSpecialtyId, (p, ps) => new { Provider = p, ProviderSpecialties = ps.DefaultIfEmpty()})
                    .SelectMany(a => a.ProviderSpecialties
                    .Select(b => new { Provider = a.Provider, ProviderSpecialties = b }))
                    .Where(w => w.ProviderSpecialties.isPrimary == true)
                    .Where (w => w.Provider.providerId == providerId)
                    .Select(m => new ProviderDetail
                    {
                        providerId = m.Provider.providerId,
                        NPI = m.Provider.NPI,
                        licenseNumber = m.Provider.licenseNumber,
                        licenseExpiration = m.Provider.licenseExpiration,
                        licenseStateId = m.Provider.licenseState,
                        licenseStateName = m.Provider.State.abbreviation,
                        networkLevelId = m.Provider.networkLevel,
                        networkLevelName = m.Provider.NetworkLevel1.name,
                        firstName = m.Provider.firstName,
                        lastName = m.Provider.lastName,
       

Open in new window

0

LINQ Query

578

Solutions

419

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
>