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

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
Want Experts Exchange at your fingertips?
LVL 9
Want Experts Exchange at your fingertips?

With Experts Exchange’s latest app release, you can now experience our most recent features, updates, and the same community interface while on-the-go. Download our latest app release at the Android or Apple stores today!

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
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
Hi Experts,
I need help setting a default value to 0, when the returned value is null, or 0.
I have tried the code below but I get a blank\empty field in my table.  See code below, and let me what I am doing wrong.

CODE
var paper = (db.Manifests.Where(m => m.ID == ID && m.ProcessedDate.Value.Month == DateTime.Today.Month).Sum(m => m.Claims != null  ? m.Claims : 0)).Value;
vals.Add((paper.ToString().Length > 0) ? string.Format("{0:###,###}", paper) : "0");

Open in new window



Thank you,
mrotor
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(), 

Open in new window

0
how to get coming expires before 7 month  in linq
0
I have BPM (Method Directive) in Epicor 10.1.600.

BO:  JobEntry

Method:  ChangeJobHeadProdTeamID

The user trigger its from the Job Entry screen by changing the "Prod Team" field.  The BPM has a Post-Processing directive, which looks like this:
foreach (var ttJobHead_Row in ttJobHead)  //ttJobHead
{
  using (var txScope = IceContext.CreateDefaultTransactionScope())
  {
    //get the header row and update it
    var jobheadrow = (from JobHead_Row in Db.JobHead.With(LockHint.UpdLock)
                      where JobHead_Row.Company == ttJobHead_Row.Company
                      && string.Compare(JobHead_Row.JobNum, ttJobHead_Row.JobNum, true) == 0
                      select JobHead_Row).FirstOrDefault();
               
               if (jobheadrow != null)
               {
                 jobheadrow.Serial_c = callContextBpmData.Character01;
                 jobheadrow.Height_c = Convert.ToInt32(callContextBpmData.Number01);
                 jobheadrow.Width_c = Convert.ToInt32(callContextBpmData.Number02);
                 jobheadrow.PaintColor_c = callContextBpmData.Character02;
                 
               } 
      
    }
    
    Db.Validate();
    txScope.Complete();    

  }
   
}

Open in new window


It executes successfully, and brings me back to the Job Entry screen.  When I select "Save" on the Job Entry screen, I get the error:
error
I have tried putting in:
var jobEntryBO = Ice.Assemblies.ServiceRenderer.GetService<Erp.Contracts.JobEntrySvcContract>();
var currentJob = jobEntryBO.GetByID(ttJobHead_Row.JobNum);
this.dsHolder.Attach(currentJob);

Open in new window


before the line:
txScope.Complete();

But still receive the error.  Would you know what is wrong here?
0
Below codes is to call Twilio API and get the sms historical conversation for one phone no. However, it takes over 15 seconds and the total records is actually just 5 conversation.

Have any experts worked with Twilio in the past and know another way to get SMS conversation in a faster mode?
Thanks,



TwilioClient.Init(accountSid, authToken);
            var messages2 = MessageResource.Read().Where(x => x.From.ToString() == fromPhoneNo);
            var messages3 = MessageResource.Read().Where(y => y.To == fromPhoneNo);
            var all = messages2.Concat(messages3).OrderByDescending(z => z.DateCreated);

Open in new window

0
Free Tool: IP Lookup
LVL 9
Free Tool: IP Lookup

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

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

Hi Experts,
I need help with a 'System.ObjectDisposedException' error I am getting in my C#.net Mvc.Net application.
I have posted my code below.

Code where I retrieve data from the database
private static List<InboundReject> getRejects()
{
    using (Utilities.RejectLetter db = new Utilities.RejectLetter())
    {

        var _rejectLetter = from m in db.InboundRejects
                            join c in db.InboundManifests on m.InboundManifestID equals c.InboundManifestID
                            where m.LetterPrintDate == null
                            select m;

        return _rejectLetter.ToList();
    }
}

Open in new window


Code where I attempt to use the data retrieved from the database
NOTE: the last statement reject.InboundManifest.Market.ToString() is what is throwing the error.
List<InboundReject> Rejects = getRejects();
foreach (InboundReject reject in Rejects)
{
    generateLetters(
        reject.DCN.ToString(), 
        reject.Description.ToString(),
        reject.BillingProviderName.ToString(),
        reject.BillingProviderAddress.ToString(),
        reject.BillingProviderCity.ToString(),
        reject.BillingProviderState.ToString(),
        reject.BillingProviderZip.ToString(),
        reject.EDOS.ToString(),
        reject.TotalCharge.ToString(),
        reject.PatientLName.ToString(),
        reject.PatientFName.ToString(),
        reject.InsuredID.ToString(),
        reject.PatientAccountNumber.ToString(),
        reject.InboundManifest.Market.ToString());
}

Open in new window


Thank you in advance for your help,
mrotor
0
I'm getting some odd and inconsistent behavior from Entity Framework 6 / MVC 5 when including related records in a Linq query

Example:
var timecards = db.Timecards.Include("Purchases.Authorizations").ToList();

Open in new window


That grabs a list of time cards, and normally includes a related parent item and a list of related records (authorization numbers, in this case). This has worked every time until recently.

Now that list will SOMETIMES include the related records and sometimes it won't. There's no error message, it just has null values for those properties. The data is there in the database, and foreign keys are all intact.

You can run this again 5 seconds later and a different random subset of the list will have missing info.

Any idea why? Or how I can force this to work every time?
0
I need to get a random record from  sql database with Linq query in entity.
0
I trying to create a query similar to below.    

I want a list of opportunities by FKVCCenterID.  But I have to go right back to the organisation and then to the VCCentres get this.

VCCentres have Organisations
Organisations have Opportunities


This query works except it returns an aynonmous type instead of the type i want which is tblOpportunities

  var opportunities = (from org in _ctx.tblVCCenterOrganisations
                join vc in _ctx.tblOrganisations on org.FKOrganisationID equals vc.OrganisationID
                join ops in _ctx.tblOpportunities on org.FKOrganisationID equals ops.FKOrganisationID
                select new
                {
                    FKVCCenterID = org.FKVCCenterID,
                    OpportunityID = ops.OpportunityID,
                    Title = ops.Title,

                }
            ).Where(x => x.FKVCCenterID == 999).Take(take).Skip(skip);


If i specify the type "select new tblOpportunity()" the where clause breaks because the tblOpportunity does not contain the FKVCCenterID
  var opportunities = (from org in _ctx.tblVCCenterOrganisations
                join vc in _ctx.tblOrganisations on org.FKOrganisationID equals vc.OrganisationID
                join ops in _ctx.tblOpportunities on org.FKOrganisationID equals ops.FKOrganisationID
                select new tblOpportunity()
                {
                    OpportunityID = ops.OpportunityID,
                    Title = ops.Title,

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