LINQ Query

496

Solutions

372

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 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
Enroll in June's Course of the Month
LVL 9
Enroll in June's Course of the Month

June’s Course of the Month is now available! Experts Exchange’s Premium Members, Team Accounts, and Qualified Experts have access to a complimentary course each month as part of their membership—an extra way to sharpen your skills and increase training.

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
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
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
[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 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
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
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
Free Tool: ZipGrep
LVL 9
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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
hi there ,
when the form is load i load all the data to the customer entity class
var cust = EF.GetCustmoer(long cid);
now i have all the data in the cust object
when the form closed i want to have indicator if there was any change in the data so i collect again all the data from all the textbox to a new
instance of cust called new_cust

now i want to compare between the cust and the new_cust
how can i do it in a simple way ?
thanks...
0
i have a data from database which includes some columns. one of them is ExpiryDate. it has SAP datetime format : MM/dd/YYYY nn:mm:ss AM

2017-01-15_14-32-18.png
i wanna convert it from MM/dd/YYYY hh:mm:ss AM to YYYY-MM-dd . Is it possible?

   deliveriesItemsbypaging.Convert(q => q.ExpiryDate.ToString("yyyy-MM-dd")).Tolist() <DeliveryItem>;

Open in new window


is it possible by using linq?
0
What is the join three tables with Lambda?
0

LINQ Query

496

Solutions

372

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
>