LINQ Query

561

Solutions

412

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

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
Cloud Class® Course: CompTIA Healthcare IT Tech
LVL 12
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

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
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
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.

I have a Linq in VB that I need to display in a gridview:
Dim myLnq = From x In Db.tbl
                                  Where x.field = "foobar" 

Open in new window


I have tried following the link below but don't quite understand what needs doing:

https://www.experts-exchange.com/questions/27632834/return-LINQ-results-and-bind-to-gridview-VB-NET.html
0
Hi

I wants til insert a xml node/node with child into an existing XML fil.
But I need it to be after a specific node :) -  "OrderReference"

The Existing File looks like this :
<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href=""?>
<Invoice xmlns="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2" xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2" xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2" xmlns:ccts="urn:oasis:names:specification:ubl:schema:xsd:CoreComponentParameters-2" xmlns:ext="urn:oasis:names:specification:ubl:schema:xsd:CommonExtensionComponents-2" xmlns:sdt="urn:oasis:names:specification:ubl:schema:xsd:SpecializedDatatypes-2" xmlns:udt="urn:un:unece:uncefact:data:specification:UnqualifiedDataTypesSchemaModule:2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2 UBL-Invoice-2.0.xsd">
	<ext:UBLExtensions>
		<ext:UBLExtension>
			<cbc:ID>1</cbc:ID>
			<ext:ExtensionAgencyID>XX</ext:ExtensionAgencyID>
			<ext:ExtensionAgencyURI/>
			<ext:ExtensionContent>
				<EG>
					<EGBIdent></EGBIdent>
					<EGStatus></EGStatus>
					<EGVersion></EGVersion>
					<EGValdat></EGValdat>
					<EGAfdeling></EGAfdeling>
					<EGSupplier>
						<cbc:ID schemeID=""></cbc:ID>
					</EGSupplier>
				</EG>
			</ext:ExtensionContent>
		</ext:UBLExtension>
	</ext:UBLExtensions>
	

Open in new window

0
product.MediaSet is a List How can I add job.Result to p.MediaSet?

                    (from job in jobs
                 join product in products on job.Result?.Name equals product?.Scene7ImageSet
                 select product.MediaSet = job.Result).ToList();

Open in new window

0
Hi, I want to store the values of one column (ColumnD) as a List<int> to use it as a look up later on.

           List<int> query = (from t in db.TABLE where t.RecordID == recordID 
                            group t by new { t.ColumnA, t.ColumnB,t.ColumnC }
                            into grp
                            select new { grp.ColumnD}).ToList();

            return query;

Open in new window


The code above gives me the following error:
'cannot implicitly convert from anonymous Generic.List type <..> to Generic List<int>'

if i return the query as an object instead, it works. Then I can look for a value using this:
var x = data.Find(a => a.ColumnD == [a value]);

Open in new window


But how can I store the values of one column in a list??

Thanks!
0
Hi I am trying to workout the best way to "push" data from an Excel Addin to a SQL Server.  The data in Excel is a predetermined and validated dataset (so I know it will insert fine).  I want to use an Excel Addin I am going to develop to insert the data to a predetermined SQL server table.  Would linq work to do this?  I so an tips would be appreciated.

Thanks in advance

Andrew
0
How do I join master locations table to the below lambda expression?

In SQL I would write:

SELECT * 
FROM ProviderLocations p
JOIN Location l
ON p.locationId = l.locationId
WHERE p.providerId = @providerId
AND p.isActive = true

Open in new window



            return PMTDBContext.ProviderLocations
               .Where(w => (w.providerId == providerId && w.isActive == true)).ToList();

Open in new window



Thanks.
0
I am needing to compare values in two lists which are different types. What I need to do is find out if the AttendeeID is in both lists. If it is in both lists then do nothing, however, if the AttendeeID is not in both lists then we need to add that information to another list. I was trying to use LINQ to do this, however, I'm not doing something correctly and I'm not sure as to where I'm wrong. Below is the code that I currently have.

If TypeOf e.Item Is GridDataInsertItem Then
        
            'Remove individuals from list if they exist already in the outcome memos table before binding the list.
            Dim newClients As RadDropDownList = CType(e.Item.FindControl("rdlClientNames"), RadDropDownList)
            Dim getAllClients As New IndividualsBase
            Dim indList As List(Of IndividualsBase) = getAllClients.SelectIndividuals(0,True,Nothing,0)

            Dim getIndividuals As New OutcomesMemos
            Dim indMemo As List(Of OutcomesMemos) = getIndividuals.SelectOutcomeMemos("Active")
            Dim totalInds As List(Of IndividualsBase)

            indMemo = indMemo.Where(Function(xMemos) Not indList.Contains(xMemos.AttendeeID)).ToList()

            If totalInds.Count = 0 Then
                Dim list As New RadDropDownList
                list.Items.Item(0).Text = "All Individuals Have Been Added"
            Else
                newClients.DataSource = totalInds
                newClients.DataTextField = "FullNameID"
                

Open in new window

0
We have a huge list (say 100,000) which needs to be converted to DataTable for SqlBulkcopy.

Can you guide what could be the fastest way to do this without using for loop? For now we are doing it like - in below code listDos is object list

using (var dataTable = new DataTable(dataTableName))
{
    dataTable.Locale = CultureInfo.CurrentCulture;
    var columns = new[]
        {
             new DataColumn("Id", typeof(int)),
             new DataColumn("FkId", typeof(int)),
             new DataColumn("Status", typeof(string)),
             new DataColumn("RecordFrom", typeof(DateTime))
        };

    dataTable.Columns.AddRange(columns);


    foreach (ObjectDo listDo in listDos)
    {
        var row = dataTable.NewRow();

        if (rebuildDo.Id != null) row["Id"] = rebuildDo.Id;

        if (rebuildDo.FkId!= null) row["FkId"] = rebuildDo.FkId;

        row["Status"] = rebuildDo.Status;

        row["RecordFrom"] = rebuildDo.RecordFrom;

        dataTable.Rows.Add(row);
    }

    return dataTable;
}

Open in new window

0
I am trying to create a select list ...that basically just creates a user entry for my user to select options to be inserted into the database.

I have this in my controller...which Is not complete  Here roles is a list of value and label.....What am I missing here..and how would you then use this to display on view....to I need something in my view model as well?

       private IEnumerable<SelectListItem> GetRoles()
        {
            IContactEntityEditService contactEntityEditService = DependencyResolver.Current.GetService<IContactEntityEditService>();
            List<string> roles = contactEntityEditService.GetContactLinkedEntitiesRoles();
           
            roles.Select(x => new SelectListItem
                                {
                                    Value = x.value,
                                    Text = x.label
                                });

            return new SelectList(roles, "Value", "Text");
        }

Open in new window

0
Cloud Class® Course: Microsoft Azure 2017
LVL 12
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Hi,

I want to merge two data sheets with different columns in c#..

For example,

Based on my sheets and column mention, it should merge the data and display in final output as datatable.

Sheet1 ==> columnA data and columnC data

sheet3 ==> columnD data and Column F data

datatable result ==> merge of ColumnA & ColumnC as one column

                               merge of ColumnD & ColumnF as another column

my input string is sheet and column.

for example,

inPut string Sheets ==> "Sheet1" and "Sheet3"

Input string Columns ==> ColumnA & ColumnC(Sheet1)

                                       ColumnD & ColumnF(Sheet3)...

How to form code in either LINQ or C#..?
0
Hi,

How to split and get value based on my count.

For example,

I have inputcount = 2
MyString = "ABC_21-NIM-3547-789_23-278";

i want to get the final string is "ABC_21-NIM". because my input is "2". so i want to split("-") and get the values.

so final output after   splitting
 ABC_21-NIM ( here ABC_21 is first string and NIM is second string)

how to form the values using LINQ C#.?
0
It really is a simple question yet finding so much trouble with Google finding how to do it. After the where clause I want to return the results from my strongly typed dataset table with a parameter passed from a parameter with vb.net and how to call it. If you can assist I would appreciate it.

Bolded is where I think it should go. But I clearly don't know how to say it. "test" would be something like
"row.Item(columnname) IsNot DBNull.Value AndAlso row.Item(columnname) = filter AndAlso row.TT_Rec_ID = Template_ID" just for example. But the way I see it we should be able to pass anything through that is LINQ compatible.

Function Global_Table_Query([b]ByVal test As Predicate(Of ds2.JOINEDDATADataTable[/b])) As DataTable
        ErrorMessage = ""

        Dim dt As New DataTable
        Dim Is_Good As Byte = 1
               Dim thread As New Task(
  Function()
      Try

          Dim lowNums = From row In Global_Table
                        [b]Where test
[/b]                        Select row


          dt = lowNums.CopyToDataTable
                    Return dt
      Catch ex As Exception
          ErrorMessage = ex.Message
          Return Nothing
      Finally

      End Try
  End Function
            )

        Dim tasks() = {thread}
        Array.ForEach(tasks, Sub(tx) tx.Start())
        Task.WaitAll(tasks)

        For Each tsk In tasks

            Select Case True

                Case tsk.IsCanceled
                    Is_Good = 1

                

Open in new window

0
Hi,

I have two data table and i want to return duplicate rows in second table if any duplicate found for same combination.

if does not match rows then it should return as below

dttable.png
the above example empno and sub1 from TAB1 should check in TAB2.

if both match then no need to worry.

if doesn't match the particular row (101 and A) then we should return rows from TAB2

below is my sample code.

but, it shows some error..

where is problem in my code...?

 foreach (DataRow row in dt1.Rows)
         {
            EmpNo = row["EmpNo"].ToString();
            Sub1 = row["Sub1"].ToString();

            var duplicates = dt2.AsEnumerable()
            .GroupBy(dr => row["EmpNo"].ToString() && row["Sub1"].ToString())
            .Where(g => g.Count() > 1)
            .Select(g => g.First())
            .ToList();

            
         }

Open in new window


pls. check my code...?
0
Hi,

currently i am using SQL Server table in one of my application.

But, my client asking me to change the table to excel file or ini file which is located in server path.

the reason is to change to excel or ini file is, my client doesn't have access to change the name of the value in table.

For example,

Table structure is below.

EmpNo      Sub1      Sub2      Sub3      Sub4
1      100      200      400      500
2      200      300      400      500
3      400      500      300      400
instead of the above table client's want to maintain in excel which need to be read from the folder.

see my sql code.

SqlCommand cmd = new SqlCommand(@"select Sub1,Sub2,Sub3 where Empno=@EmpNo", sqlcon);
                    cmd.Parameters.Add("@EmpNo", SqlDbType.NVarChar).Value = cmbEmp.SelectedItem.ToString();

 the same i want to convert and read from excel or ini.

How to change..?
0
Hi Experts,
I am getting an error in LINQ expression that I use in MVC.net with C#.net code behind application.
It's complaining about how I am handling a null date value.
I need help resolving this error

Here is the error:

An exception of type 'System.NotSupportedException' occurred in EntityFramework.SqlServer.dll but was not handled in user code

Additional information: LINQ to Entities does not recognize the method 'System.DateTime Parse(System.String)' method, and this method cannot be translated into a store expression.


Here is my code:
            List<DataObject.HS> _lstTasks = new List<DataObject.HS>();
            using (CDO.CAEntities db = new CDO.CAEntities(new Security().getConnectionString(1), UserID, ID))
            {
                _lstTasks = (from m in db.HS
                             select new DataObject.HS
                             {
                                 ID = (int)m.ID,
                                 IssueDescription = m.IssueDescription,
                                 ResolvedDate = m.ResolveDate == null ? DateTime.Parse("1/1/1900") : (DateTime)m.ResolveDate,
                             }).ToList();
            }
            return _lstTasks;

Open in new window

0

LINQ Query

561

Solutions

412

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
>