LINQ Query





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

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()
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 *
select pd.ProductName,year(dsm.CreatedDate)as [Year],sum(dsm.ProductQuantity)as ProductQuantity
from dbo.DatewiseStockDetailMaster dsm
left join dbo.StockMaster sm on
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
for [Year]
as pivotTable

Open in new window

How to convert from sql to linq using pivot
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.

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:

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="" xsi:schemaLocation="urn:oasis:names:specification:ubl:schema:xsd:Invoice-2 UBL-Invoice-2.0.xsd">
						<cbc:ID schemeID=""></cbc:ID>

Open in new window

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

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

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

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

In SQL I would write:

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

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"
                newClients.DataSource = totalInds
                newClients.DataTextField = "FullNameID"

Open in new window

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


    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;


    return dataTable;

Open in new window

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

The 14th Annual Expert Award Winners
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!


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

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#.?
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 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(

          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

      End Try
  End Function

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

        For Each tsk In tasks

            Select Case True

                Case tsk.IsCanceled
                    Is_Good = 1


Open in new window


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

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


Open in new window

pls. check my code...?

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..?
The codes below is working ok. I just need to make VAR response to be sorted.
How can I do that?

   public static List<Make> GetVehicleMakeList()
            List<Make> lstMake = new List<Make>();
            HttpClient objClient = new HttpClient();
                var response = objClient.GetStringAsync(Utility.Enumeration.vehicleMakeAPICall).Result;
                if (!string.IsNullOrEmpty(response))
                    VehicleMakeAPIResponse objMakeResponse = Newtonsoft.Json.JsonConvert.DeserializeObject<VehicleMakeAPIResponse>(response);
                    return objMakeResponse.Results;

            catch (Exception ex)

                //log error message here
            return lstMake;

Open in new window

Hi Experts,
I am getting an error in LINQ expression that I use in with 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,
            return _lstTasks;

Open in new window

Even if no recipients are checked and this clears all of the recipients in the grid, this is as expected.
Please find the code file in text file.

This is fragment of my .cs code

        public arAbonamenty_page()

            Nexo_ARWALEntities myNexo_ARWALEntities = new Nexo_ARWALEntities();

            Nullable<Int32> grupa_tw = new Nullable<Int32>();
            grupa_tw = null;
            short? kiedy_fakturować_combo = new Nullable<Int16>();
            bool? bool_okres_płatności = new Nullable<bool>();
            if (okres_płatności.SelectedIndex == 2)
                bool_okres_płatności = null;
                bool_okres_płatności = Convert.ToBoolean(okres_płatności.SelectedIndex);

            Nullable<bool> kiedy_fakturować = new Nullable<bool>();
            kiedy_fakturować = Convert.ToBoolean(kiedy_fakturować_combo);

            var query = (from myabonamenty in myNexo_ARWALEntities.ArAbonamenty
                         join nexotowary in myNexo_ARWALEntities.Asortymenty
                         on myabonamenty.nexo_towar equals nexotowary.Id
                         join myklienci in myNexo_ARWALEntities.ArKlienci
                         on myabonamenty.id_klient equals myklienci.id_klient
                         where (grupa_tw == null || (grupa_tw != null && nexotowary.Grupa_Id == grupa_tw))
                         where (myabonamenty.czy_fa_auto_na_początku_okresu == null || (kiedy_fakturować != null && myabonamenty.czy_fa_auto_na_początku_okresu == kiedy_fakturować))


Open in new window

Get your problem seen by more experts
LVL 12
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

How to add a new plan if state name = 'NJ' and type name = 'Term Life'? and carrier name = 'Mutual of Omaha' using c#?

plan name="test"
plan id = "123"
plan healthtier="test"

	<State Name="NJ">
		<Type Name="Subsidized">
			<Carrier Name="Chip" ID="100000008">
				<Plan Name="Chip" ID="100000322" HealthTier="" />
		<Type Name="Term Life">
			<Carrier Name="Mutual of Omaha" ID="100000017">
				<Plan Name="Term Life Express" ID="100000259" HealthTier="" />
<State Name="PA"> 
		<Type Name="In Market">
			<Carrier Name="2017 Capital Blue Cross" ID="100000134">
				<Plan Name="Silver PPO 4500/0/10" ID="100002633" HealthTier="Silver" />
			<Carrier Name="2017 Geisinger Health Plan" ID="100000135">
		<Type Name="Out Market">
			<Carrier Name="2017 Capital Blue Cross" ID="100000134">
				<Plan Name="Gold PPO 1000/0/20" ID="100002634" HealthTier="Gold" />
			<Carrier Name="2018 Capital Blue Cross" ID="948120063">
				<Plan Name="Gold PPO 1000/0/20" ID="100002634" HealthTier="Gold" />
		<Type Name="Term Life">
			<Carrier Name="Assurity" ID="100000005">
				<Plan Name="Non-Med Term 350" ID="100000230" HealthTier="" />

Open in new window

var query =
     (from c in [b]db.productInfo[/b]
     where [b]c.flavor[/b] == "Classic Coke" && [b]c.container[/b] == "Can"
     select new { c.co2Target }).Single();

Open in new window

Can the parts in bold be decided at runtime?

In other words, I want to use a context.<Table> and use LINQ lambda expression to do a select on the table, but I don't know which table until runtime.

Can I pass in the name of the table as a string into a method, and then inside the method use a lambda expression to do a select on <table name passed in> ?  Build the lambda expression dynamically?
I want to do the same thing as the code below -- but using LINQ in C# ( LINQ to SQL ) to do the iterating and inserting.  Please provide exact C# code.  The broader question is how to do basic CRUD operations in LINQ.  I do have a context called "Model1":

I want just very simple statements ... nothing complex.  Just basic CRUD operations.

I managed to get a SELECT working .. but I am not sure how to do an INSERT or UPDATE.

 using (var db = new Model1())
//do a select
var query = from b in db.Customers
                            orderby b.CustID
                            select b;

                //    //Console.WriteLine("All blogs in the database:");
                foreach (var item in query)

 using (var db = new Model1())
// do the insert 

 using (var db = new Model1())
// do the update

 using (var db = new Model1())
// do the delete 

Open in new window

private void btnInsert_Click(object sender, EventArgs e)
    foreach (DataGridViewRow row in dataGridView1.Rows)
        string constring = @"Data Source=.\SQL2008R2;Initial Catalog=AjaxSamples;Integrated Security=true";
        using (SqlConnection con = new SqlConnection(constring))
            using (SqlCommand cmd = new SqlCommand("INSERT INTO Customers VALUES(@CustomerId, @Name, @Country)", con))
                cmd.Parameters.AddWithValue("@CustomerId", row.Cells["Id"].Value);
                cmd.Parameters.AddWithValue("@Name", row.Cells["Name"].Value);
                cmd.Parameters.AddWithValue("@Country", row.Cells["Country"].Value);
    MessageBox.Show("Records inserted.");

Open in new window


I use to connect to a SQL database.

I have a order table, and a orderline table.

Dim recOrderHeaders As IList(Of ShopifyData.OrderHeader)

            recOrderHeaders = (From item In dbContext.OrderHeaders
                               Where (item.CustID = MyCustomer.CustID)
                               Order By item.OrderNo Descending
                               Select item).ToList

' Then I iterate through all the orders and find the order lines
for each item in recOrderHeaders
   for each oLine in recOrderheaders.Orderlines

Open in new window

Iteration through order headers go quickly, but iteration through order lines takes a long time (on my case 60ms per line).

I thought all data was collected in the OrderHeader object from memory when using the ToList.

Typically each order has 1-5 lines.

How can I speed this up ? For example one customer has 90 orders and it takes 5 seconds to get all orders/orderlines.
Hi experts
I'd some research on comparing two lists of integers but can't find the things I need.

I have two lists of integers. User will only have the old list.
I would like to determine a course of actions that instructs a user how to produce the new list from the old list.

old list:  1 2 3 4
new list:5 7 2 3 4

What I need are actions such as:
1) Remove item "1" from index 0
2) Add item "5" to index 0
3) Add item "7" to index 1

Thanks in advance

PS. I am using .NET 4.0
I have xml below, and If i just give you  'applicantFirstName' and the value is 'John'
how can we add
'John' into       <FirstName ID="applicantFirstName" PrefillValue="No">John</FirstName>?

based on two requirements

1. I will only pass you  Dictionary<string, string> e.g. <applicantFirstName,'john'>
2. You actually do not know the structure of xml except you know each element has "ID" attritbute. meaning that you pass 'applicantFfirstName'' only into xml, and try to find element, and add value (John) there.

so you won't be able to use linq like doc.Descendants("Applicant").FirstOrDefault()

Let me know if this is possible.

        <GeneralInformation UI="BondInfo">
          <Name ID="bondName" PrefillValue="Yes">Contractor</Name>
          <State ID="bondState" PrefillValue="Yes">CA</State>
          <Code PrefillValue="Yes">888</Code>
          <Renew PrefillValue="Yes">
          <Amount PrefillValue="Yes">15000</Amount>
          <Year PrefillValue="Yes">1</Year>
          <CompanyName ID="applicantCompanyName" PrefillValue="No"></CompanyName>
          <CompanyEntityType ID="applicantCompanyEntityType" PrefillValue="No"></CompanyEntityType>
          <CompanyTaxID ID="applicantCompanyTaxID"></CompanyTaxID>

Open in new window


LINQ Query





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