LINQ Query

495

Solutions

371

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

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
Free Tool: Port Scanner
LVL 8
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

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
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 '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
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 8
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.

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
my str will look like 111,222,3333

in where statement - can you share with me what I should do below?

    public List<Domain.Document> returnProposalList(string str)
        {
            //get state/carriername/planname
            Repository.DocumentDataContext ddc = new Repository.DocumentDataContext();
            return
                (
                from tblCarrierProductsData in ddc.TblCarrierProducts
                join tblDocuments in ddc.TblDocuments
                on tblCarrierProductsData.CarrierProductID equals tblDocuments.CarrierProductID
                where (tblCarrierProductsData.CarrierProductID == str && tblCarrierProductsData.State == "CA")             
                select new Domain.Document()
                {
                    carrierProductID = tblCarrierProductsData.CarrierProductID,
                   
                    documentID = tblDocuments.DocumentID,                    
                    documentType = tblDocuments.DocumentType,
                    note = tblDocuments.Note,
                    pdf = tblDocuments.PDF
                }                
                ).ToList();            
        }

Open in new window

0
Below is my linq / c# and it return duplicated record. All I need to return one "blue shied"
How can I do that?


    public List<Domain.Carrier> GetCarrierList()
        {
            Repository.CarrierInformation.GenericDataContext gdc = new Repository.CarrierInformation.GenericDataContext();
            return (
                    from tblCarriersData in gdc.TblCarriers
                    join tblCarrierContactsData in gdc.TblCarrierContacts
                    on tblCarriersData.CarrierID equals tblCarrierContactsData.CarrierID
                    orderby tblCarriersData.Name
                    select new Domain.Carrier()
                    {
                        name = tblCarriersData.Name,
                        carrierID = tblCarriersData.CarrierID
                    }
                ).ToList();
        }

Open in new window


Result:
Blue Shield
Blue Shield
Blue Shield
Blue Shield
0
I'm creating a DropDownListFor using this code:
@{
    attributes.Clear();
    attributes.Add("id", Regex.Replace(question.QUESTION.Replace(' ', '_'), "[^a-zA-Z0-9_]", "").Replace("__", "_"));
    attributes.Add("Name", "question.ANSWER_LOOKUP_OPTION_ID");
    attributes.Add("class", "form-control");
    if (question.IsReadOnly)
    {
        attributes.Add("disabled", "disabled");
    }
}
@Html.DropDownListFor(m => question.ANSWER_LOOKUP_OPTION_ID, question.LookupGroup, attributes)

Open in new window

This is the LookupGroup values (converted to JSON):
[
    {
      "Disabled":false,
      "Group":null,
      "Selected":false,
      "Text":"< 1 yr.",
      "Value":"8"
    },
    {
      "Disabled":false,
      "Group":null,
      "Selected":true,
      "Text":">= 1 yr. and < 2 yrs.",
      "Value":"9"
    },
    {
      "Disabled":false,
      "Group":null,
      "Selected":false,
      "Text":">= 2 yrs. and < 3 yrs.",
      "Value":"10"
    },
    {
      "Disabled":false,
      "Group":null,
      "Selected":false,
      "Text":">= 3yrs.",
      "Value":"118"
    }
]

Open in new window

This is the ANSWER_LOOKUP_OPTION_ID value: 9

How can I clear the selected flag for the one with the selected set to true using LINQ?
0
On Demand Webinar: Networking for the Cloud Era
LVL 8
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.

Hello all,

I have a table CustomerAccount that has a field called TransactionDate that is loaded to with the same transaction date about 3k records a day.   I need to get all the records from this table where the TransactionDate is the latest date.   I also need to make sure performance is optimal.   What is the best way to do this?   I also have the same for another table but that table does not have an identity column.  So for example for the Customer table I came up with this:

  var customers = from n in GetList<CustomerModel>()
                                group n by n.CustomerID into g
                                select g.OrderByDescending(t => t.TransactionDate).FirstOrDefault();

I also don't know if that is optimal either.   If that is fine I will use that but also have the other table without an identity so wondering if I should do something different.

Thanks
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
Hi Guys,

I have table in sql database with column that gives random number when you insert new row.
Now I built MVC application that going to insert new rows to this table and the only column increase by itself is the ID.
I need to create unique number for the "code";

how can I check if the number exist in the code column and if not insert 6 number that not exist.
any idea will be blessed.
 
Capture.PNG
0
When I add the following line [legalName = If(String.IsNullOrEmpty(b.Suffix), b.FullName, Convert.ToString(b.FullName) & " " & Convert.ToString(b.Suffix)),] to my query I receive the following error.

Exception Details: System.InvalidOperationException: Could not translate expression 'ToString($VB$It1.$VB$It1.b.Suffix)' into SQL and could not treat it as a local expression.

Here is how I place code in my query.
Dim query As Object = (From b In db.ROLCBasicInfos Order By b.LastName, b.FirstName
                                   Join ui In db.ROLCUserInfos On b.RecordID Equals ui.memberID Where ui.memberStatus <> "Discharged"
                                   Join ma In db.ROLCMemberAddresses On b.RecordID Equals ma.memberID
                                   Join ai In db.ROLCAdditionalInfos On b.RecordID Equals ai.memberID
                                   Select b.RecordID,
                            b.LastName,
                            b.FullName,
                            legalName = If(String.IsNullOrEmpty(b.Suffix), b.FullName, Convert.ToString(b.FullName) & " " & Convert.ToString(b.Suffix)),                            
                            ma.fullAddress,
                            workPhone = If(String.IsNullOrEmpty(ma.homePhone), ma.mobilePhone, ma.homePhone),
                            ma.emailAddress,
                            ui.memberStatus,
                            ai.dob)

Open in new window


I'm not sure what is causing the problem.  Logically, it should only concatenate if "Suffix" is not Null or Empty, so converting an empty string should not occur.  But if I'm reading the error correctly, that is what it is saying.  

Am I correct in my understanding?
What am I missing?
How do I correct this issue?
0
Hello, I have a list of school objects that contain a list teacher objects from this I need to select a new object which contains the school-name, City, comma separated teacher-email

School
name
address
city
List<Teacher>

Teacher
name
email
grade

How can this be accomplished using Linq to query the list of schools?
0
Hi,
I have a Linq query that runs a search in 2 tables (through a "Join"):

First table is tblProducts where master data for any product is saved (Name, Barcode, ItemCode, EnglishName, SpanishName, etc.)
Second table is the warehouse/stocks table, every warehouse have specific stock in it so in here we save the warehouse number and the stock of each product.

When I run the search via VB.Net form for a product called "encendedor" (in spanish) it does not appears in the query results:

Dim result = From t1 In objEntities.tblProducts Join t2 In objEntities.tblWarehouseProducts On t1.Barcode Equals t2.Barcode Where ((t2.WarehouseID = Warehouse) And (t2.InStock > 0)) And (t1.Barcode.Contains(CodBarra) And (t1.SpanishName.Contains(Descripcion) And ((t1.ProductCode.Contains(CodigoSAP) And (t1.ExtCatalogNo.Contains(CodProveedor) And (t1.Brand.Contains(Branch))))))) Select t1

Open in new window

take a special attention to:
(t1.SpanishName.Contains(Descripcion) 

Open in new window

where "Descripcion" brings the "encendedor" string.

Now, if I run the query using simple sql commands using SQL Server Management Studio it do finds the result:
select id, USERID, Barcode, ProductCode, ExtCatalogNo, SpanishName, InStock, Brand, Inactive from tblProducts where SpanishName like 'encendedor%'

Open in new window


One thing I have noticed is that if I don't add the '%' at the end of the simple sql command in SQL Server Management Studio it does not finds the result either, so how can I fix my Linq query to find it?

Please your help experts.
Thanks.
0

LINQ Query

495

Solutions

371

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
>