LINQ Query

507

Solutions

378

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 to I add "or" to the following:

 var recordCount = db.tblWHQReports.Count(a => a.Mgr == employeeid);

??
0
On Demand Webinar: Networking for the Cloud Era
LVL 9
On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

I have a record in sql server 2016 that has two time(0) columns.  I need to do a query with linq that returns the id when the current time is between the 2 stored times (starttime and endtime).

any help appreciated.

string now = DateTime.Now.ToString("HH:mm:ss");

            var result = (from s in db.Shifts
                          where s.StartTime >= DateTime.Parse(now) &&
                          s.EndTime <= DateTime.Parse(now)
                          select s.ShiftId);

Open in new window

0
Receiving an error on var found.  The problem is that the first time the code is ran this.productModel is null.  the Linq statement is to verify if the product exists before adding the value again.  I can't write

var found;

Then check to see if product.model == null.

Any idea how to work around this?

public class GetProd
{
            public ObservableCollection<ProductModel> productModel { get; set; }
            
             public void GetTheProd(string Name)
             {
                  if (App.productModel != null)
                      this.productModel = new ObservableCollection<ProductModel>(App.productModel);
                   .........
                  var found = (from prod in this.productModel where prod.Name== name select prod).ToList();
             }
}

Open in new window

0
I have the following xml file that I would like to read in c#.

It should be simple but I am having a little trouble reading the 'Items' in the file.  I would like to read the code and description for each 'item' in the file.  I would like to use linq but as I said, I am having a little trouble.

As always, all help appreciated

Thanks

David


<?xml version="1.0" encoding="UTF-8" ?>
<examplepartner xmlns="http://www.example.com/example">
<BRAND>example</BRAND>
<Styles>
<Item>
<Code>A12345</Code>
<Description>Item 1</Description>
</Item>
<Item>
<Code>A987654</Code>
<Description>Item 2</Description>
</Item>
<Item>
<Code>A65987</Code>
<Description>Item 3</Description>
</Item>
</Styles>
</examplepartner>
0
Hello Experts.

I've successfully built quite a few LINQ queries in my Windows Form project thus far, but for some reason, I am not able to get the "For Each" code to recognize the fields I'm selecting. The error condition is "[FieldName] is not a member of 'System.Array' in LINQ Query".

I did my homework and found that I needed to add Imports System.Linq in my declarations, and sure enough it was missing (which I find strange since this has been working fine for all my other LINQ queries).

My primary objective with the code is a grab a Picture from a related table, and populate a PictureBox control with the result. Here's the code I've got right now:
Private Sub MyPlayerPicture()
    ' Process Picture separately to better handle Null values
    Dim APAID As Integer = CInt(Me.MyPlayersAPAID.Text)
    Dim GetPicture =
         (
             From RosterData In SNAPDataSet.RosterView
             Where Not RosterData.IsNull("Player_Picture")
             Select RosterData.Player_Picture
         ).ToList
    If GetPicture.Count = 0 Then
        Me.MyPlayersPicture.Image = My.Resources.Silhouette
    Else
        For Each row In GetPicture
            If row.APA_Player_ID = APAID Then
                Dim ppBlob() As Byte = row.Player_Picture
                Dim ppStream As New MemoryStream(ppBlob)
                Me.MyPlayersPicture.Image = Image.FromStream(ppStream)
                Exit Sub
            End If
        Next row
    End If
End Sub

Open in new window


The lines with error conditions are the ones which reference row.[FieldName]. I can also confirm that Intellisense doesn't recognize row as associated with the .

I have tried several other permutations of the above before posting this including removing the Where clause, as well as fetching single records by setting the Where clause to "Where Not RosterData.IsNull("Player_Picture") AndAlso RosterData.APA_Player_ID = APAID".

BTW, this subroutine is called by its predecessor which populates other fields on the same form when the record is changed as shown here:

Open in new window

0
I looked at some of the other related questions, but didn't find a good answer. My TSQL query works fine:

select PORel.ReqNum,PORel.ReqLine,XFiles.* from E10DB.Erp.PORel PORel
JOIN E10DB.Erp.ReqDetail ReqDetail ON ReqDetail.Company = @company AND 
      ReqDetail.ReqNum = PORel.ReqNum
JOIN E10DB.Ice.XFileAttch XFiles ON XFiles.Company = @company AND 
       XFiles.Key1 = CONVERT(nvarchar(50),ReqDetail.ReqNum)
WHERE PORel.PONum = @ponum    

Open in new window


I would like to translate this into a LINQ result like:

var joinResultRows = from PORel , etc.,..........  

Open in new window


so I can do loop through them to do something :
 foreach(var joinResult in joinResultRows)
 {
     string custID = joinResult.CustID;
     string ponum = joinResult.PONum;
 }    

Open in new window


Any thoughts? Thanks!
0
I am performing a LINQ query to find duplicates in a DataTable.  It is working perfectly with Option Strict OFF.  However, for compliance purposes, I must have Option Strict ON.  Example-A is the original code.  In Example-B, I modified the syntax to get it to compile, but it fails at runtime.

Dim dtDivision4_Results As DataTable
<Fill DataTable>


Example-A: LINQ query when: Option Strict OFF

This code runs properly and returns duplicates in dtDupAccounts

 Dim dtDupAccounts = dtDivision4_Results.AsEnumerable().GroupBy(Function(i) i.Field(Of String)("AccountNumber")).Where(Function(g)
 g.Count() > 1).Select(Function(g) g.Key)


Example-B: LINQ query when: Option Strict ON

The code compiles, but at runtime, I get an "Invalid Cast Exception".

 Dim dtDupAccounts As DataTable = CType(dtDivision4_Results.AsEnumerable().GroupBy(Function(i) i.Field(Of String)
 ("AccountNumber")).Where(Function(g) g.Count() > 1).Select(Function(g) g.Key), DataTable)


The exact exception I get is:

+            $exception      {"Unable to cast object of type 'WhereSelectEnumerableIterator`2[System.Linq.IGrouping`2[System.String,System.Data.DataRow],System.String]' to type 'System.Data.DataTable'."}      System.InvalidCastException


Please help me perfect the syntax of the query in Example-B.
I am a novice to VB.Net and your help in correcting my syntax would be greatly appreciated.
0
We are updating our website and now have two set of members.  One set of those who have registered and one set of those historical members who have yet to re-register.  I am looking for the ability to merge(union) those two tables for a search functionality however I am struggling with the union.

In SQL
select  member_id, ssn
from member a 
union 
select  member_id, ssn
from member_historical

Open in new window


LINQ
     var union = (from members in db.members
                    select new
                    {
                        members.member_id,
                        members.ssn
                    }
                ).Union
                    (from member_historical in db.member_historical
                     select new
                    {
                        member_id = member_historical.member_id,
                        ssn = member_historical.ssn
                    }
                );

Open in new window


When I return view(union) an error:  Argument type 'system.linq.Iquerable<member_id;int ssn:string}>' is not assignable to model type of 'system.collections.generic.IEnumberable<Data.member>'



Tried to cast:  return View((IEnumerable<member>) union);


I'm a  little lost, any help or guidance is appreciated.
0
I have the following query and am getting the error:
LINQ to Entities does not recognize the method 'Int32 ToInt32(System.String)' method

Any idea why Convert.ToInt32 cannot be used?  How would I revise this to get it working?

string LookupCompany = "DD";
string LookupTable = "Price";
string LookupPartNum = "107";
decimal LookupConfigValue = 3360;
var PriceFound = (from ROW in Db.PcLookupTblValues
                                join c in (
                                    (from a in Db.PcLookupTblValues
                                     join b in (
                             (from PcLookupTblValues in Db.PcLookupTblValues
                              where
PcLookupTblValues.Company == LookupCompany &&
PcLookupTblValues.LookupTblID == LookupTable &&
(PcLookupTblValues.ColName == "PartNum" &&
PcLookupTblValues.DataValue == LookupPartNum)
                              select new
                              {
                                  PcLookupTblValues
                              })) on new { RowNum = a.RowNum } equals new { RowNum = Convert.ToInt32(b.PcLookupTblValues.RowNum.ToString()) } into b_join
                                     from b in b_join.DefaultIfEmpty()
                                     where
                           a.Company == LookupCompany &&
                           a.LookupTblID == LookupTable &&
                           a.RowNum == b.PcLookupTblValues.RowNum &&
                           (a.ColName == "MinValue"

Open in new window

0
I have a linq query (I am new to this)... it is returning a list of row numbers that are of type int in the database.

Here is the query:
var query = from A in (
	(from PcLookupTblValues in Db.PcLookupTblValues
	where
	  PcLookupTblValues.Company == "DD" &&
	  PcLookupTblValues.LookupTblID == "Price" &&
	  (PcLookupTblValues.ColName == "PartNum" &&
	  PcLookupTblValues.DataValue == "107")
	select new {
	  PcLookupTblValues
	}))
select new {
  A.PcLookupTblValues.RowNum
}

Open in new window


I need to take this list of rows and go through each one finding which row I want to use using another Linq query.

How would I get these into a list and traverse through this list using linq?
0
[Webinar] Learn How Hackers Steal Your Credentials
LVL 9
[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

I am trying to run json linq query in Visual Studio 2008. I am getting the error :

select is not a member of newtonsoft.json.linq.array

Below is my code

Dim APIURL As String = "https://mydomain/api/v1/enrollments/search?id=12345"
            Dim req As WebRequest = WebRequest.Create(APIURL)
            req.ContentType = "application/json; charset=utf-8"
            req.Credentials = New System.Net.NetworkCredential("xyz", "123")
            req.Method = WebRequestMethods.Http.Get

            Dim text As String = ""
            Dim dt As DataTable = New DataTable
            Dim res As Stream = req.GetResponse().GetResponseStream
            Dim reader As New StreamReader(res)
            Dim response As String = reader.ReadToEnd()

            reader.Close()
            res.Close()

              Dim parsedObject = JObject.Parse(response)

            Dim docs = JArray.FromObject(parsedObject("enrollments")).Select(Function(x) x.ToObject(Of jSonClass.Enrollment)()) 'Error here
EE_json.txt
0
I have this code

public ActionResult GRIDPRINTS_Read(int woid, [DataSourceRequest]DataSourceRequest request)
        {
            IQueryable<GRIDPRINTS> gridprints = db.GRIDPRINTS;
            DataSourceResult result = gridprints.ToDataSourceResult(request, c => new GRIDPRINTS
            {
                WOID = c.WOID,
                GRIDPRTID = c.GRIDPRTID,
                GRIDID = c.GRIDID,
                MAPNAME = c.MAPNAME,
                SCALE = c.SCALE,
                PAGENUMBER = c.PAGENUMBER,
                ERRORS = c.ERRORS,
                STATUS = c.STATUS,
                NOTIFICATION = c.NOTIFICATION
            });

            return Json(result, JsonRequestBehavior.AllowGet);
        }

Open in new window


IT works fine Except I want to pass the woid to a where clause in the results.  So I added this line

.where(c.WOID==woid)    

public ActionResult GRIDPRINTS_Read(int woid, [DataSourceRequest]DataSourceRequest request)
        {
            IQueryable<GRIDPRINTS> gridprints = db.GRIDPRINTS;
            DataSourceResult result = gridprints.ToDataSourceResult(request, c => new GRIDPRINTS
            {
                WOID = c.WOID,
                GRIDPRTID = c.GRIDPRTID,
                GRIDID = c.GRIDID,
                MAPNAME = c.MAPNAME,
                SCALE = c.SCALE,
                PAGENUMBER = c.PAGENUMBER,
                ERRORS = c.ERRORS,
                STATUS = c.STATUS,
                NOTIFICATION = c.NOTIFICATION

             .where(c.WOID==woid)   

            });

            return Json(result, JsonRequestBehavior.AllowGet);
        }

Open in new window


It is failing with unknown method "where of string"  I am sure it is something simple stupid but after an hour of trying everything I am turning to the experts.
0
i have an scenario where i have to compare  live data from the database to the data in the customer file that we get every night and send only the changes between the two.

i am trying to create a join in LINQ where i need to join two columns, customer and status to that of the file.
if its the same customer and same status but all the other columns are different then write it as changed customer but if its the same customer and different status then i would like to update that customer record to a 4 which means removed customer. this is my linq code i am not sure how to do a join on multiple columns and how to do an update.

i would like to join customers and status on the below code

     DataTable tblSamebutotherchanges = (from r in FirstDataTable.AsEnumerable() 
                                                join s in SecondDataTable.AsEnumerable()
                                                on r.Field<string>("Customer") equals s.Field<string>("Customer")
                                                where r.Field<string>("STATUS").Trim() == s.Field<string>("STATUS").Trim() || r.Field<string>("Name").Trim() != s.Field<string>("Name").Trim() || r.Field<string>("ADDRESS").ToUpper().Trim() != s.Field<string>("ADDRESS").ToUpper().Trim() || r.Field<string>("ADDRESS 2").Trim() != s.Field<string>("ADDRESS 2").Trim() || r.Field<string>("CITY").ToUpper().Trim() != s.Field<string>("CITY").ToUpper().Trim() || r.Field<string>("STATE").Trim() != 

Open in new window

0
Hi Experts,
I need help with a writing a lamba expresion for my C# application.

I have put the SQL table samples below (REJECTS,and MANIFEST tables), as well as the output I want to get back.
I want the output to return everything from the REJECTS table and the StoreID column from the MANIFEST table.  The only column that the tables have in common is the BatchName column.  How can I write the lambda expression to achieve my desired output?

TABLES
SQL TABLES
DESIRED OUTPUT
DESIRED OUTPUT
Thank you very much in advance,
mrotor
0
Hello, I have an XML with repeating nodes that I am trying to read with Linq.  Here is sample XML

<Tax>
   <TaxType>Monthly Use</TaxType>
   <TaxDescription>Monthly Use</TaxDescription>
   <TaxAmount>34.01</TaxAmount>
   <TaxRate>9.00000</TaxRate>

</Tax>
<Tax>
   <TaxType>CapCostReduction</TaxType>
   <TaxDescription>CapCostReduction</TaxDescription>
   <TaxAmount>128.83</TaxAmount>
   <TaxRate>5.00000</TaxRate>
</Tax>

Open in new window

Here is my Linq code so far.
 Dim doc As XDocument = XDocument.Load(inFile)

            Dim taxSegs As IEnumerable(Of String) = 
                From seg In doc...<Tax>
                Select desc = seg...<Tax>.Where(Fuction(x) x.Name = "TaxDescription").Value

Open in new window


Thanks for any help
0
Hi guys,

I have query that I run in sql and I'm trying to get the same result with linq:

Here is my sql query:
use RMSmasterHQ
select  b.ID, a.ItemLookupCode, b.ParentItem,
case when b.ID IS NULL or b.ID <> 0 THEN 'PARENT' END AS IsParent 
from Item a
left join dbo.Item b on a.ID = b.ParentItem 

Open in new window


Here is my linq which I'm trying to do the same I do with sql but it gives me different result:
RMSmasterHQEntities db = new RMSmasterHQEntities();
            var get = (from i in db.Items
                       join b in db.Items on i.ID equals b.ParentItem into group1
                       from g1 in group1.DefaultIfEmpty()
                       where g1.ParentItem == 126031 && g1.ID != 0 && i == null
                       select i).ToList();

Open in new window


Please, can somebody help.
Thanks,
0
Hi Experts.

I have a grid that displays a comma separated list of a users roles using  the following LINQ :

Roles.Text = string.Join(", ", rolesList.OrderBy(x => x.Name ).Select(x => x.Name));

(x is the Role name)

The list of Roles contains one called 'User' that everyone gets, and I need to hide this from the list, is there a simple where clause to the LINQ that would achieve this please?

Andy
0
I am creating a repository pattern in .NET.  I want to pass in the SQL statement then return the results as a list.  Want to make it so I'm not setting it to an object so the select can be used anywhere and not limited to what is calling it.  

Any ideas?
0
Is there a solution using LINQ to build a new string based on the following criteria?

Concatenate each uppercase letter to the left of each # , then include everything after the last #. A few examples:

Name#FormOfAddress becomes NFORMOFADDRESS
PostalAddress#DeliveryAddress#AddressLine1 becomes PADAADDRESSLINE1

Thanks!
0
On Demand Webinar - Networking for the Cloud Era
LVL 9
On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

This is the best option I found,
https://stackoverflow.com/questions/10538395/modifying-linq-results

but cant figure this line
var orderlist = from x in cqsDC.MasterQuoteRecs

Open in new window


First intention was, MasterQuoteRecs its just a mistake, has to be MasterQuote, like the class name, but of course it wasnt.

So, I have no Idea how that comes together?
0
I have an interesting issue that has kept me googling for a couple of days.

I am executing a stored store procedure that implements Offset Fetch Next for custom .net paging.

The entity framework can't seem to figure out the correct return type.  It thinks I am trying to return an int instead of a result set.  Therefore I can not use select.

sproc code:  

      @param1 bit = null,
      @param2 bit = null,
      @pageNumber int = 0,
      @rowsPage int = 10

SELECT        id, name
FROM          table
WHERE
            (@param1 IS NULL OR (param1 = @param1 ))
            AND (@param1 IS NULL OR (param1  = @param1 ))
                              
      ORDER BY name DESC
      OFFSET ((@pageNumber - 1) * @rowsPage) ROWS
      FETCH NEXT @rowsPage ROWS ONLY

Linq Code.  

var result = context.storedProceure(params).Select(s => new object { blah = s.blah, blah1 = s.blah1}).ToList();

Error: 'int' does not contain a definition for 'Select' and no extension method 'Select' accepting a first argument of type 'int' could be found (are you missing a using directive or an assembly reference?)      

Linq can figure out the complex result type if I remove the Offset Next Command.

How can I help the entity framework figure out the result type?

I've tried:

SET NOCOUNT ON;

SET FMTONLY OFF;

neither seem to make a difference...
0
I am trying to get a count of the max value of times where the times intersect.

The expected result that I want need from the code example below should be 4.

There are a total of 8 times, there are 6 values that intersect in total, a group of 4, and a group of 2.

What I am trying to get is the max value of intersections but just cant get it to work.

This is the code as it stands at the moment.

void Main()
{
var times = new List<Times> {
new Times
        {
            Start = DateTime.Now,
            End = DateTime.Now.AddMinutes(10)
        },
new Times
        {
            Start = DateTime.Now,
            End = DateTime.Now.AddMinutes(10)
        },
new Times
        {
            Start = DateTime.Now.AddMinutes(2),
            End = DateTime.Now.AddMinutes(10)
        },

new Times
        {
            Start = DateTime.Now.AddMinutes(15),
            End = DateTime.Now.AddMinutes(35)
        },
new Times
        {
            Start = DateTime.Now.AddMinutes(25),
            End = DateTime.Now.AddMinutes(42)
        },
new Times
        {
            Start = DateTime.Now.AddMinutes(43),
            End = DateTime.Now.AddMinutes(50)
        },
new Times
        {
            Start = DateTime.Now.AddMinutes(55),
            End = DateTime.Now.AddMinutes(89)
        },
new Times
        {
            Start = DateTime.Now.AddMinutes(2),
            End = DateTime.Now.AddMinutes(12)
        }
};


times.OrderBy(x => x.Start);

var overlappingEvents =
   

Open in new window

0
So i'm trying to familiarize myself with the expression syntax of linq, you know the upside down SQL stuff; it's a bit of a change from the method based which i'm used to, but not too bad; however i can't for the life of me figure out the equivalent of the following

class Program
{
    static void Main(string[] args)
    {
        var nums = Enumerable.Range(0, 10);
        var chars = "Hello world".ToCharArray();
        var names = new string[] {"Pawel", "Magda", "Tomek" };

        var result = nums.Select(n => n.ToString()).Concat(names).Concat(chars.Select(c => c.ToString()));

        Array.ForEach(result.ToArray(), s => Console.Write(s + ", "));
        Console.WriteLine();
    }
}

Open in new window


in the expression syntax.
0
I am trying to join 2 objects.  ProductData and ProductJoin.  My code sample is below.  I want to get all of ProductData and the Top 1 of ProductJoin sorted on date descending so I get the last entry entered into that object.  If there is no join I still want ProductData.

The method I have the Linq logic in is TestProductRepo()
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Test
{
    public class Product
    {
        public class ProductData
        {
            public string PropertyName { get; set; }
            public string Description { get; set; }
            public string Value01 { get; set; }
            public string Value02 { get; set; }
            public string Value03 { get; set; }
        }

        public class ProductJoin
        {
            public string PropertyName { get; set; }
            public string Category { get; set; }
            public DateTime ProductDate { get; set; }
        }

        public class ProductRepo
        {
            public string PropertyName { get; set; }
            public string Description { get; set; }
            public string Category { get; set; }
        }

        public Product()
        {
            List<ProductData> productData = new List<ProductData>();
            productData.Add(new ProductData { PropertyName = "Property 001", Description = "Property 001 Desc", Value01 = "Value01", Value02 = "Value02", Value03 = 

Open in new window

0
Below is some sample code.  I am trying to figure out how to use LINQ and select data from a List<> and return it to an object.  Code I am trying to resolve is located below the ?????.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Test
{
    public class Product
    {
        public class ProductData
        {
            public string PropertyName { get; set; }
            public string Description { get; set; }
            public string Value01 { get; set; }
            public string Value02 { get; set; }
            public string Value03 { get; set; }
        }

        public class ProductRepo
        {
            public string PropertyName { get; set; }
            public string Description { get; set; }
        }

        List<ProductData> _productData = new List<ProductData>();

        public Product(List<ProductData> productData)
        {
            _productData = productData;

            //???????

            //var result = from x in _productData
            //             select new List<ProductRepo>()
            //             {
            //                 x.PropertyName,
            //                 x.Description
            //             };

            List<ProductRepo> productRepo = (from x in _productData
                                             select new List<ProductRepo>()
                                             {
                                   

Open in new window

0

LINQ Query

507

Solutions

378

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
>