Expiring Todayâ€”Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x

# LINQ Query

520

Solutions

383

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.
I have a collection of elements. Each element is identified by a string key composed of 6 string subkeys labeled A,B,C,D,E,F.

The full key is produced by concatenating together the 6 string subkeys and inserting a delimiter between each subkey. Say '.' is the delimiter character.

So a key might be "12.34.56.78.90.10"
and the subkeys are:
A=12
B=34
C=56
D=78
E=90
F=10

A subkey string may also be empty.
e.g.
A key might be "12.34.56.78"
and the subkeys are:
A=12
B=34
C=56
D=78
E=""
F=""

I wish to find a "best match" given specific values for A,B,C,D,E,F, where some of those values may be the empty string.

A "best match" is determined by the following:
For example, given A,B,C,D

a. Look for an exact match to {A,B,C,D,-,-}
b. Look for an exact match to {A,B,C,D,*,-}
c. Look for an exact match to {A,B,C,D,*,*}
d. Look for an exact match to {A,B,C,-,-,-}
e. Look for an exact match to {A,B,-,-,-,-}
f. Look for an exact match to {A,-,-,-,-,-}
g. Give up.

- means the empty string ""
* means we'll accept anything that's not an empty string.

The general idea is if we don't have an exact match, then we first look deeper for a match, and if nothing is found, then we look shallower for a match, until we find a match or exhaust our options.

A few caveats:

If A=5 then do the above but ignore values B & C (accept anything including -)
If A=6 then do the above but ignore value C (accept anything including -)
If A=4 and D is â€¦
0
ow to merge multiple list values in one list values.

For example,

List<string> List1 = new List<string> {"A","B","C"}; // Some times null value also

List <string> List2 = new List<string> {"D","E","F"}; // Some times null value also

List <string> List3=  null;  // Some times null value also

My Query is below :

List<string> ArrAllValue = List1.Concat(List2.Concat(List3))
.Where(x => !string.IsNullOrEmpty(x)) //probably
.ToList();

Error : Value Cannot be null
0
I want to find the columns from my table based on my input string.

for example,

my table as below.

So, First i want to find the columns of "Lesson x SUB A" and the get the values of that..

My Result is = 400..

How to write LINQ Query or C# code..?

``````DataTable table = new DataTable();
table.Columns.Add("Lesson1 x SUB A");
table.Columns.Add("Lesson1 x SUB B");
table.Columns.Add("Lesson1 x SUB C");
table.Rows.Add(400,500,600);
table.Rows.Add(300, 400, 500);
table.Rows.Add(500, 600, 700);
string Findcolumns = "Lesson1 x SUB A";
``````

Below is the simple Query.

``````string result=table.Rows[0][Findcolumns].ToString();
``````

But, how to give where condition in the query.

because, i have "n" number of rows and i want to filter based on the conditions..

for example,

``````DataTable table = new DataTable();
table.Columns.Add("Lesson1 x SUB A");
table.Columns.Add("Lesson1 x SUB B");
table.Columns.Add("Lesson1 x SUB C");

table.Columns.Add("UNIT");

table.Rows.Add(400,500,600,205);
table.Rows.Add(300, 400, 500,300);
table.Rows.Add(500, 600, 700,400);
``````

Where unit = 400.
0
How to find the exact particulate word from a string using LINQ.

MyString = "10"-550-HO-004-01-12XVRU-XA";

MyPositionNo(Input)  : 4

Result : 004

MyPositionNo(Input)  : 1

Result : 10"

Also,

this is not matching word. This needs to be find the position (Index) of the string to get.

Example.

InputParam = 4 (it means 4th position of my string)

So, 4th position is : 004 (MyString = "10"-550-HO-004-01-12XVRU-XA")

004 ==> is my 4th position want to fetch.

1st position is : 10" (MyString = "10"-550-HO-004-01-12XVRU-XA")

10" ==> is my 1st position want to fetch.
0
Below codes is to return .net datatable, and I use asp.net repeater to bind it. everything is working except the list does not sort by date and time. The data source starts from query3.

Not sure why. and hope some experts can help out.

Thanks

var query3 = query1.Concat(query2).OrderBy(z => z.DateTimeSent);

``````public static DataTable GetConversation(string sid, string token,string accountPhoneNo, string twilioPhoneNo)
{
string clientPhoneNo = string.Empty;
string inputFilePath = filePathUserMessagesAll;
DataTable dt = new DataTable();
dt.Clear();
dt.Columns.AddRange(new DataColumn[]
{
new DataColumn("Body",typeof(string)),
new DataColumn("DateCreated",typeof(string)),
new DataColumn("Direction",typeof(string)),
new DataColumn("SelectedPhoneNo",typeof(string))
});
XDocument xdoc = null;
using (XmlReader xr = XmlReader.Create(inputFilePath))
{
xdoc = XDocument.Load(xr);
var query1 = from t in xdoc.Descendants("User")
where
(
t.Element("From").Value.ToLower() == "+1" + accountPhoneNo.ToLower()
) //client phone
select new
{

``````
â€¦
0
Hello Experts,
I need help converting a SQL query to LINQ, to use in my C#.Net application.
Please provide a query that returns a sql value, and one that returns a list (please see code below).

``````SELECT OutPutPath FROM  [dbo].[ProcCon] PC
INNER JOIN ProcType PT ON
PT.TypeID = PC.TypeID
WHERE PT.Name = 'TimeInterval'
``````

Thank you,
mrotor
0
Hi,

I done some sample code which should search matching record and display in specified format.

i am unable to get exact format.

my output is : 100,300 (because only matching ENGLISH H and MATHS L)

see my sample code's
0
Hi Experts!

Need your help updating LINQ query.

The LINQ query should return a distinct record of locationId and phonenumber.
Then which need to extract locationId and phonenumber and store it in local variables.

SQL Query:

``````SELECT DISTINCT
personlocation.LocationID,
phone.PhoneNumber
FROM dbo.personProducts pp
INNER JOIN dbo.personPhonesLocations phoneLocation ON pp.personLocationRecID = phoneLocation.personLocationRecID
INNER JOIN dbo.personLocations personlocation ON pp.personLocationRecID = personlocation.personLocationRecID
INNER JOIN dbo.Phones phone ON phoneLocation.PhoneID = phone.PhoneID
INNER JOIN dbo.PhoneTypes PhoneTypes ON pl.PhoneTypeID = PhoneTypes.PhoneTypeID
WHERE pp.PersonNumber = 'ABC123'
AND PhoneTypes.PhoneTypeName = 'Contact Number'
``````

C# method:
``````var personPhone = (from pp in dataContext.personProducts
from phoneLocation in dataContext.personPhonesLocations
from personLocation in dataContext.personLocations
from phone in dataContext.Phones
from phoneType in dataContext.PhoneTypes
where pp.PersonNumber == "ABC123" && phoneType.PhoneTypeName == "Contact Number"
select new
{
LocationId = personLocation.LocationID,
phonenumber = phone.PhoneNumber
}).Distinct();

if (personPhone != null)
{
string locationId = ???; <-- need help
string phonenumber = ??  <-- need help

}
``````

TIA!
0
How to return row based on finding columns and its value

List<string> Str = [0] Sub A=100

[1] Sub B=80

now i want to find the column of Sub A and Sub B in my datatable.

If column found then find the row of the above column values for condition basis.

for example (as per example data table).

Sub A value is 80 ==> this should check in between Sub A(MN) and Sub A(MX) &&
Sub B value is 100 ==> this should check in between Sub B(MN) and Sub B(MX)

5. Final result would be return true/false (sample example returns true)

Tried below Query

string[] str = { "Sub A=80, Sub B=100"}; // Range should check between range of minimum and maximum of both subjects

DataTable dt = new DataTable();
dt.Columns.AddRange(new[] { new DataColumn("TYPE"),new DataColumn("Sub A(MN)"), new DataColumn("Sub A(MX)"), new DataColumn("Sub A(MN)"),
new DataColumn("Sub B(MX)") ,new DataColumn("Sub A") ,new DataColumn("Sub A1"),new DataColumn("FROM"),new DataColumn("TO"),new DataColumn("DEPT") });
dt.Rows.Add(null, "100", "1000", "100", "100", null, null, 20, "100", "S6");
dt.Rows.Add(null, "200", "2000", "200", "200", "200", "200", "35", "100", "S7");
dt.Rows.Add(null, "100", "1000", "100", "100", "100", "100", "35", "100", "S7");
dt.Rows.Add(null, "100", null, null, "100", "100", "100", "35", "100", "S7");
dt.Rows.Add(null, "100", "1000", "100", null, null, "100",â€¦
0
Hi,

How to find and search list values which is available or not from multiple another lists.

For example,

List<string> FromList = new List<string>() { "Sub A", "Sub B" };
List<string> List1 = new List<string>() { "Sub A=80", "Sub D=70" };
List<string> List2 = new List<string>() { "Sub B=100", "Sub C=90" };

I want to take Fromlist and check this will be available or not in List1 and List2.

if it is available then it returns result as Sub A=80 and Sub B=100.

if it is not available in both list (List1 and List2) then it returns false.

How to form in LINQ Query..?
0
How to I add "or" to the following:

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

??
0
Hi ,

I am passing list value into the datatable.

I want to find the column first and then check whether the value exist or not for that columns.

I want to write in LINQ Query as bool true/false

for example,

string str ABSCHECK = ABS A#ABS B // input value

string[] words1 = str.Split('#'); //Splitting the values

words1[0] ABS A //list

[1] ABS B

The above two value is a column name and i want to check in the below table.

DTTABLE :

A      ABS A(MN)      ABS A(MX)      ABS B(MN)      ABS B(MX)      ABS C(MN)      ABS C(MX)
100      100      100      100      100      100      100
For ABS A ==> Matching two columns and values present(like ABS A(MN) and ABS A(MX))

For ABS B ==> Matching two columns and values exists (like ABS B(MN) and ABS B(MX))

So i want to return result is true.

if nothing is matching, it means (column not identified and value not present) returns false.

How to write LINQ..?

below is my query

string str ABSCHECK = ABS A#ABS B // input value

string[] words1 = str.Split('#'); //Splitting the values

bool results = (from r in DTTABLE.AsEnumerable()
select r.Field<string>(words1.ToList())); // to find out columns and value

but not working..?
0
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);
``````
0
Hi,

I want to check some word which contains some + symbols. when i write the below query it should return true.

but, i am getting object reference error.

Also my condition is should check null value in column2 and column3

below is my code:

Inparam1 = "A+B";
bool Result1 = DT1.AsEnumerable()
.Any(row => (row.Field<String>("Col1").contains(Inparam1)) && (row.Field<String>("col2") == null) && (row.Field<String>("col3") == null)));

The above code should return "TRUE" as per my below referring table. But, it returns "Object reference Error"

DT1 Table

Col1      Col2      Col3
A+B/D+EB
A/D+EB
A+B/X+A1
A      2      3
what is the problem and where i want to change the code..?
0
i have a method that takes two datatable and does a inner join using LINQ and then i am using a where condition where i tell it to only give me the records where the status does not equal status from the other datatable but the values returned has matches. so what am i doing wrong in the code below?

``````DataTable RemoveCustomers = (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()
select s).CopyToDataTable();

foreach (DataRow dr in RemoveCustomers.Rows)
{

// string cust = dr["Customer"].ToString();
tblDiff.Rows.Add(dr["CUSTOMER"].ToString().Trim(), dr["NAME"].ToString().Trim(), replaceapostrophe(dr["ADDRESS"].ToString().Trim()), replaceapostrophe(dr["ADDRESS 2"].ToString().Trim()), replaceapostrophe(dr["CITY"].ToString().Trim()), dr["STATE"].ToString().Trim(), dr["ZIP CODE"].ToString().Trim(), dr["PARENT"].ToString().Trim(), dr["TELEPHONE"].ToString().Trim(), dr["FAX"].ToString().Trim(), dr["TERMS OF PAYMENT"].ToString().Trim(), dr["COUNTRY"].ToString().Trim(), dr["CURRENCY"].ToString().Trim(), "[b]4[/b]", dr["LONG"].ToString().Trim(), dr["LAT"].ToString().Trim(),
``````
â€¦
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();
}
}
``````
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
``````

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:
``````
``````
â€¦
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
``````

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

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

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

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

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
}
);
``````

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"
``````
â€¦
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
}
``````

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
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);
}
``````

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);
}
``````

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

# LINQ Query

520

Solutions

383

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