LINQ Query

560

Solutions

411

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

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
Free Tool: IP Lookup
LVL 12
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Hi,

I'm trying to get the third level of a related table using ThenInclude but it's not working.

Please advise

Code
0
Hi,


I'm trying to create a linq that returns me the sum of a list which is simple, but I have a list inside a list that would be more or less the scenario:

object has lstObject1 and lstObject1 has lstObject2

no object has a key field id in lstObject2 has a field name and in lstOjbect3 has the field I want to add Qtd

How to create a linq that owes me a list with id, name, summit qtd?
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
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
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.

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
Hello Friends,
I have a chart which i have built using the linq query which is as below
 
public List<DatewiseStockDetailViewModel> getProductSoldByYear(int CompanyId)
{
var userData = (from m in datewise.GetAll()
join s in stock.GetAll()
on m.Stock_Id equals s.Stock_Id
join p in product.GetAll()
on s.ProductId equals p.ProductId
where s.CompanyId == CompanyId && m.IsProductDeducted == true
group new { m, s, p } by new { Convert.ToDateTime(m.CreatedDate.ToString()).Year, p.ProductName, p.ProductId } into g
select new DatewiseStockDetailViewModel
{
productId = Convert.ToInt32(g.Key.ProductId),
productName = g.Key.ProductName,
ProductQuantity = g.Sum(x => x.m.ProductQuantity),
year = g.Key.Year.ToString()
}).Distinct().ToList<DatewiseStockDetailViewModel>();
return userData;
}

Open in new window


This is the current output which i am getting
But i want to write a code by which i am able to use the pivot code in linq to get the output as
ProductName         2016         2017         2018
cedar oil                  NULL        16         34
computer                 NULL         8               1
grain                        NULL   21       NULL
Keyboard                  2           3       NULL
marie                        NULL        26     NULL
Pine Oil                     NULL         4                  5
harabhara kebab      NULL            5               1
 
For this i have written the sql query
 
select *
from(
select pd.ProductName,year(dsm.CreatedDate)as [Year],sum(dsm.ProductQuantity)as ProductQuantity
from dbo.DatewiseStockDetailMaster dsm
left join dbo.StockMaster sm on
dsm.stock_Id=sm.stock_Id
left join dbo.ProductDetails pd
on sm.ProductId=pd.ProductId
where sm.CompanyId=17 and dsm.IsProductDeducted=1
group by year(dsm.CreatedDate),pd.ProductName
)as SourceTable
pivot
(
sum(ProductQuantity)
for [Year]
in([2016],[2017],[2018])
)
as pivotTable

Open in new window


How to convert from sql to linq using pivot
0
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
Free Tool: SSL Checker
LVL 12
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

LINQ Query

560

Solutions

411

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
>