Go Premium for a chance to win a PS4. Enter to Win

x

LINQ Query

535

Solutions

393

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 Experts,
I am getting an error in LINQ expression that I use in MVC.net with C#.net code behind application.
It's complaining about how I am handling a null date value.
I need help resolving this error

Here is the error:

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

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


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

Open in new window

0
Free Tool: IP Lookup
LVL 11
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.

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

Image
0
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"

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

Open in new window

0
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?
0
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)
                {
                    Console.WriteLine(item.CustID);
                }
}

 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);
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }
    MessageBox.Show("Records inserted.");
}

Open in new window

0
Hi

I use VB.net 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
   next
next

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

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

<Surety>
  <SuretyLine>
    <Commercial>
      <Bond>
        <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">
            <Year>1</Year>
          </Renew>
        </GeneralInformation>
        <Coverage>
          <Amount PrefillValue="Yes">15000</Amount>
          <Year PrefillValue="Yes">1</Year>
        </Coverage>
        <Applicant>
          <CompanyName ID="applicantCompanyName" PrefillValue="No"></CompanyName>
          <CompanyEntityType ID="applicantCompanyEntityType" PrefillValue="No"></CompanyEntityType>
          <CompanyTaxID ID="applicantCompanyTaxID"></CompanyTaxID>
          <FirstName 

Open in new window

0
get alert on line if (elementID == update.Key) because update.key is string.
how can i fix it?

       
 public void CreateQuoteFile(Dictionary<string, string> changes)
        {
            string fileName = CreateXmlfile("License","CA");
            XDocument doc = XDocument.Load(fileCreateQuotePath+fileName);
            foreach (var update in changes)
            {
                var element = doc.Descendants(update.Key).FirstOrDefault(); //element from XML file
                var elementID = doc.Descendants(update.Key).Select(b=>b.Attribute("ID")).FirstOrDefault(); //element ID from XML file

                //if (elementID != null) 
                if (elementID == update.Key)
                {
                    element.Value = update.Value;
                }
            }
            string fileNameNew = Guid.NewGuid().ToString() + ".xml";
            doc.Save(fileCreateQuotePath + fileNameNew)

Open in new window

;
0
Below codes is reading my xml Applicant
and try to find Element e.g. CompanyName (var element = doc.Descendants(update.Key).FirstOrDefault();)
But I try to find ID, how can i do that?
My goal is to find ID
like applicantCompanyName = update.value


public void CreateQuoteFile(Dictionary<string, string> changes)
        {
            string fileName = CreateXmlfile("CA");
            XDocument doc = XDocument.Load(fileCreateQuotePath+fileName);
            foreach (var update in changes)
            {
                var element = doc.Descendants(update.Key).FirstOrDefault();
                if (element != null)
                {
                    element.Value = update.Value;
                }
            }

            string fileNameNew = Guid.NewGuid().ToString() + ".xml";
            doc.Save(fileCreateQuotePath + fileNameNew);            
        }



<Applicant>
<CompanyName PrefillValue="No" ID="applicantCompanyName"/>
<CompanyEntityType PrefillValue="No" ID="applicantCompanyEntityType"/>
<CompanyTaxID ID="applicantCompanyTaxID"/>
<FirstName PrefillValue="No" ID="applicantFirstName">asdfasdf</FirstName>
<LastName PrefillValue="No" ID="applicantLastName"/>
<MiddleName PrefillValue="No" ID="applicantMiddleName"/>
<StreetName PrefillValue="No" ID="applicantStreetName"/>
<City PrefillValue="No" ID="applicantCity"/>
<State PrefillValue="No" ID="applicantState"/>
<Zip PrefillValue="No" ID="applicantZip"/>
<PhoneNo PrefillValue="No" …
0
Important Lessons on Recovering from Petya
LVL 11
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Hi Experts,

I have tried to round off a value x = 455.77 using math .round() function but not rounded the value 466.

Code I have given to round off:

var rounded = Math.Round(x, 2, MidpointRounding.AwayFromZero);

Open in new window


Could you guys please explain whats wrong here?

Thanks,
Anjala Baby
0
I trying to read the attached jSon response. Also attached is the class that I am using. Below is the code to load the response in a collection so I can populate a datagrid.

Dim docs2 = JArray.FromObject(parsedObject("enrollments")).Select(Function(x) x.ToObject(Of jSonClass.enrollments)())
            Dim empList As New System.Collections.Generic.List(Of jSonClass.enrollments)
            empList = docs2.Select(Function(x) x).ToList

I am getting the error on the last line. "Object reference not set to instance of an object.
jSonClass_EE.txt
EE_jSon_Enrollments.txt
0
0
Hi

I have this section of code, using LINQ to get the data from an EF back end.

How would I change it to order by School.Name? I have tried several approaches and getting nowhere.

var school = _unitOfWork.school.Find(x => x.Specialty != null && x.Specialty.Id == specialtyId).ToList();
                var schoolDTOs = _schoolMapper.Map(school);
                return schoolDTOs;

Andy
0
Hi,

How to write LINQ query to split and find the column min and maximum and return based on string values.

I done some code using LINQ, but it is not working.

what is the problem in my code.

Scenario is,

1. Split the string and find the column

2. Check the minimum and maximum range of each splitted string. (SUB C=100#SUB D=200)

3 range should check within the limit like 100 min and max

Hi,

How to write LINQ query to split and find the column min and maximum and return based on string values.

I done some code using LINQ, but it is not working.

what is the problem in my code.



 protected void Button_Click(object sender, EventArgs e)
    {

        
        string string1 = "SUB C=100#SUB D=200";

        string FinalOutput = "GRADE#SUB B";

        DataTable dt = new DataTable();
        dt.Columns.AddRange(new[] { new DataColumn("Studentno"),new DataColumn("GRADE"),new DataColumn("SUB B"), new DataColumn("SUB C(MIN)"), new DataColumn("SUB C(MAX)"),
new DataColumn("SUB D(MIN)") ,new DataColumn("SUB D(MAX)") ,new DataColumn("SUB C"),new DataColumn("FROM"),new DataColumn("TO") });
        dt.Rows.Add(101,"A", "100", "100", "200", "200", "300", null, 20, "100");
        dt.Rows.Add(101, "B", "100", "150", "250", "250", "350", null, 20, "100");
        dt.Rows.Add(101, "B", "100", "200", "300", "100", null, null, 20, "100");
        dt.Rows.Add(101, null, "100", "200", "300", "100", null, null, 20, "100");



        //Check whether a given 

Open in new window

0
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";

Open in new window


Below is the simple Query.

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

Open in new window


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

Open in new window


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
How to Use the Help Bell
LVL 11
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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
                             {
                   

Open in new window

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'

Open in new window


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'

Open in new window


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

}

Open in new window


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

LINQ Query

535

Solutions

393

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
>