LINQ Query

616

Solutions

447

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 code block like this

private bool isupdated () {
   return books.where(x=> !IsNullOrEmpty(x.edition.version))
.any(x=>x.isNew);
}

here books is a ICollection<Books> books and book class has Edition as its private members along with others.

example is contrived for this question .

I get exception executing the function above and debugging that turns out due to some bad data the edition is null for one of the books , which shouldn’t be.

as a temporary fix I put in a check in the where clause above to check for edition
like below -
 return books.where(x=> x.edition != null && !IsNullOrEmpty(x.edition.version))


but I get the same exception that edition is null.

shouldn’t this check prevent it ?
0
Why Diversity in Tech Matters
LVL 13
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

Hello EE,

I have in vb.net a dictionary of (Date, String) that looks like this :

"01/01/1980" ; "a,b,r,e,t,b,g,x,y,z"
"02/01/1980" ; "k,w,q,z,c,u,i,o,p"
"03/01/1980" ; "n,w,q,y,h,v,r"
.....
"04/28/2019" ; "b,c,r,y,z"


actually, the "string" part has always 20 letters. i did not write them all, but you understand.
My problem is, If I tell my software to look for all combination of 5 letters or let's say 6 letters... and to tell me :

for "a,b,c,d,e,f" when is the last time it was in ?
then for "a,b,c,d,e,g" when is the last time it was in ?
then for .....
then for "u,v,w,y,z,x" when is the last time it was in ?

by doing 2 FOR LOOP is long.

My main question is, is there any Functions, or special classes, or LINQ methods that are more efficient in terms of speed?

my code today is doing this :

        For x As Integer = 0 To combinaisonList.Count - 1
            Dim arrString As String() = combinaisonList(x).Split(New String() {","}, StringSplitOptions.RemoveEmptyEntries)

            For Each kvp As KeyValuePair(Of Date, String) In MyItems.Reverse
                If arrString.All(Function(v) kvp.Value.Contains(v)) Then
                    finalLoop.Add(combinaisonList(x), Math.Abs(DateDiff(DateInterval.Day, Now, kvp.Key)))
                    Exit For
                End If
            Next
        Next

Open in new window



so combinaisonList is the (List Of String) of all combinaison that I deceided lets say (6 letters)
and "MyItems" is the dictionary(of date, string) that I was talking about

My code works, but its not fast enough.
1
I have a linq group by question...

When I do the group by on assets, the record does not display at all. I want to keep it. the user may or may not have an asset.

 example..
 Name           Role                     Asset #
 John Smith  office manager 01-569450
 John Smith  office manager 01-569450
 John Smith  office manager 01-345454
 John Smith  office manager null

 I want
 John Smith  office manager 01-569450
 John Smith  office manager 01-345454
 John Smith  office manager null    <- Keep after the group by*

 Dim query = (From element In query1).OrderBy(Function(a) a.AssetNumber)
                                     .GroupBy(Function(a) a.AssetNumber)
                                     .Select(Function(a) New UserViewModel With {.ID = a.FirstOrDefault.ID,
                                                                                 .FirstName = a.FirstOrDefault.FirstName,
                                                                                 .MiddleInitial = a.FirstOrDefault.MiddleInitial,
                                                                                 .LastName = a.FirstOrDefault.LastName,
                                                                                 .RoleName = a.FirstOrDefault.RoleName,
                                                                                 .EmailAddress = a.FirstOrDefault.EmailAddress,
                                                                              …
0
I have a linq group by question...
I only want to group asset numbers where the asset number has a value. I want to keep nulls.

example..
Name           Role                     Asset #
John Smith  office manager 01-569450
John Smith  office manager 01-569450
John Smith  office manager 01-345454
John Smith  office manager null

I want
John Smith  office manager 01-569450
John Smith  office manager 01-345454
John Smith  office manager null

Here is my code:

 Dim query = (From element In query1).OrderBy(Function(a) a.AssetNumber)
                                     .GroupBy(Function(a) a.AssetNumber)
                                     .Select(Function(a) New UserViewModel With {.ID = a.FirstOrDefault.ID,
                                                                                 .FirstName = a.FirstOrDefault.FirstName,
                                                                                 .MiddleInitial = a.FirstOrDefault.MiddleInitial,
                                                                                 .LastName = a.FirstOrDefault.LastName,
                                                                                 .RoleName = a.FirstOrDefault.RoleName,
                                                                                 .EmailAddress = a.FirstOrDefault.EmailAddress,
                                                                                 .PhoneNumber = a.FirstOrDefault.PhoneNumber,
             

Open in new window

0
Here is my query.. * A user has many assets. It is displaying the user several times with the same asset. I am using a group(left join on assets) from User.

Thanks.

Example Results -

User             AssetNumber   ...

Jodi White     01-345454

Jodi White     01-345454

Dim query = (From u In _entities.Users                   
Join x In _entities.XREF_UserRole On u.ID Equals x.UserID                    
Join z In _entities.REF_Location On x.REF_LocationID Equals z.ID                     
Join ad In _entities.REF_Address On z.REF_AddressID Equals ad.ID                     
Group Join a In _entities.Assets On a.UserID Equals u.ID Into UserAssets = Group       
From userAsset In UserAssets.DefaultIfEmpty                    
Where u.IsActive                  
Join mdl In _entities.REF_Model On userAsset.REF_ModelID Equals mdl.ID                     
Select New UserViewModel With {
     .ID = u.ID,
     .FirstName = u.FirstName,
     .MiddleInitial = u.MiddleInitial
    .LastName = u.LastName,
    .RoleName = x.REF_Role.RoleName,
    .AssetNumber = userAsset.AssetNumber,
    .AssetReceivedDate = userAsset.AssetReceivedDate,
    .AssignedTo = u.FirstName + " " + u.LastName}).OrderBy(Function(a) a.LastName).ToList()

Open in new window

0
I have the following simple code with a skip and take which i'd hoped would add the following to my query e.g. "OFFSET 3000 ROWS FETCH  FIRST 10 ROWS ONLY" which runs very quick on the millions of rows with provider ibm.data.db2.entityframework.

 
public static IQueryable<T> GetPage<T>(this IQueryable<T> entities, int pageRows, int pageSize,int totalRows)
        {
            var skip = (totalRows < pageSize ? 0 : pageRows);
            return  entities
                .Skip( skip)
                .Take( pageSize);
        }

Open in new window


but instead it creates the following adding a "WHERE Project1.row_number > 0" for the skip which is not that fast:

SELECT 
Project1.CASE_ID AS CASE_ID, 
Project1.CREATED_TSTAMP AS CREATED_TSTAMP, 
Project1.CASE_TYPE_DESC AS CASE_TYPE_DESC, 
Project1.CASE_STATUS_DESC AS CASE_STATUS_DESC

FROM ( SELECT           Project1.CASE_ID AS CASE_ID, 
                        Project1.CREATED_TSTAMP AS CREATED_TSTAMP, 
                        Project1.CASE_TYPE_DESC AS CASE_TYPE_DESC, 
                        Project1.CASE_STATUS_DESC AS CASE_STATUS_DESC, 
                        row_number() OVER (ORDER BY Project1.CASE_ID ASC) AS row_number
      FROM ( SELECT 
            Extent1.CASE_ID AS CASE_ID, 
            Extent1.CREATED_TSTAMP AS CREATED_TSTAMP, 
            Extent1.CASE_TYPE_DESC AS CASE_TYPE_DESC, 
            Extent1.CASE_STATUS_DESC AS CASE_STATUS_DESC
            FROM CRSOWN.CRV_CASE_SUMMARY AS Extent1
      )  AS Project1
)  AS Project1
WHERE Project1.row_number > 3000
ORDER BY Project1.CASE_ID ASC FETCH FIRST 10 ROWS ONLY ;

Open in new window


how do I get it to add "OFFSET 3000 ROWS FETCH  FIRST 10 ROWS ONLY"
0
i have used the linq code like the example below to construct a list of customers with the respective value,

Dim bunds As IEnumerable = (From dr In duse _
                     Group dr By a = dr.Field(Of Decimal)("msisdn") Into grp = Group Select New With
                            { _
                                .msisdn = a, _
                                .data_bytes_bundle = grp.Sum(Function(x) If(x.Field(Of Decimal)("bonus") = 1, x.Field(Of Decimal)("byts"), 0)),
                                .data_bytes_bonus = grp.Sum(Function(x) If(x.Field(Of Decimal)("bonus") = 2, x.Field(Of Decimal)("byts"), 0))
                            }).ToList

I have three list which i want to put into a single collectio using linq, it is failing at  o.msisdn with massage
Object variable or With block variable not set.

 Dim result = (From c In bunds
             Group Join o In core
             On c.msisdn Equals o.msisdn Into co = Group
             From o In co.DefaultIfEmpty() Join p In pcrf On o.msisdn Equals p.msisdn
             Select o.msisdn, o.data_bytes_bundle, o.data_bytes_bonus, o.data_bytes_core, p.byt_pcrf, p.byt_pcrf_bon).ToList

I am failing to get out of this
0
json string have parent and child and child has sub child(grand child). Here wanted to remove grand child. child name and grand child name is same so if i remove based on the name it will remove child and grand child.But I wanted to remove only GrandChild property. I have converted json string to JObject here my code snippet: i know this is wrong it deletes both child and grand child because names are same. Please find attached my Json object structure and json string. I am using C#.net. Please let me know if you need any information.

JObject jObject = JObject.Parse(jsonString);
jObject.Descendants()
.OfType<jproperty>()
.Where(attr => attr.Name.Contains("NameofChild"))
.ToList()
.ForEach(attr => attr.Remove());
json-string.txt
jsonStructure.jpg
0
I have 2 tables

Groups
GroupLogs

On first load of webpage I need to just pick the firstordefault group, and load all LOGS for that GROUP

here is the query i have so far
     var _getHistoricalData = (
                    from pGroup in _mme.t_Groups
                    join pLog in _mme.t_GroupLog on pGroup.Group_ID equals pLog.fk_Groups_Id
                   where (pLog.LogTimeStamp < DateTime.Now.AddDays(-7))

Open in new window


This returns all groups and all logs
How would i select the first Group and its logs only?
0
Hi

I need to improve the performance of the some code

To give you an overview, I have an image which is 320px 240px, each pixel has temperature data associated with it
I've loaded the temperature data into a float[,]:

xytArray:
Float[320,240] - its contains x y pixel position data, and a temperature value
i.e.
[0,0] 70
[0,1] 70
[0,2] 75
etc
Capture.PNG

Then i have many RegionOfInterests which is a list of x y points, this does not contain temperature data, but i do need to use these x y points to look up the current temperature value.
 public IList<Point> RoiPointsList 

Open in new window

Capture.PNG

When i'm Foreaching through the Float[,] i'm adding the temperature data to it
This is fast, and no problem here

However, when i try and check if the RegionOfInterest has a x,y pixel for the current iteration (so i can get its temperature value), it really slows down (i'm checking 12 region of interest of each pixel in loop)
I need a much faster way to check if the RegionOfInterest has a mentioned pixel for the current x,y
Then after building the x, y RegionOfInterest with temperature data, i need to query if for min, max and mean data.


Here is the code

//make an empty float[320,240]
var xytArray = new Single[flirImage.ThermalData.Max(xy => xy.X) + 1, flirImage.ThermalData.Max(xy => xy.Y) + 1];

//populate float[,]
foreach (var xyItem in flirImage.ThermalData) // 76800 rows in flirImage.ThermalData
            {

   

Open in new window

0
Introduction to Web Design
LVL 13
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

I have created a LINQ to return data from SQL server in JSON format which contains nested values.
Attach the code and output, the JSON is showing duplicate values for FieldID:6

var query =  from g in entity.ConfigSearchFieldGroupings
                         join f in entity.ConfigSearchFieldGroupingFields
                         on g.GroupingID equals f.GroupingID
                         join d in entity.ConfigDataFields
                         on f.FieldID equals d.FieldID
                         join ss in entity.ConfigSelections
                         on d.FieldID equals ss.FieldID into t
                         from rt in t.DefaultIfEmpty()
                         join s in entity.ConfigSourceSystems
                         on d.SystemCode equals s.SystemCode
                         where g.IsActive == true
                         && d.IsActive == true
                         && s.IsActive == true
                         && g.GroupingID == siGroupingID
                         && d.IsSearchable == true
                         select new
                         {
                             g.GroupingName,
                             f.FieldID,
                             d.FieldName,
                             d.FieldText,
                             d.FieldDescription,
                             d.FieldCategory,
                             d.FieldDataType,
                             d.FieldLength,
                             

Open in new window

0
I wrote an app that uses
List<string> str = new List<string>();

Open in new window

Problem I have is I'm adding to many items to the string which is causing issues.

I perform a search in the string
var found = (from r in str
             where r == value
             select r);

Open in new window

List<string> can be replaced with a StringBuilder.  

Can you query a StringBuilder with Linq?
0
I have the following query in linq
   var lastPosRaw = (from lp in db.tblCommonTrackingDatas
                                              where lp.vpkDeviceID == deviceid && lp.vReportID == 124 && lp.dGPSDateTime <= positionDateTime
                                              orderby lp.dGPSDateTime descending
                                              select new
                                              {
                                                  vLatitude = lp.vLatitude,
                                                  vLongitude = lp.vLongitude,
                                                  vTextMessage = lp.vTextMessage
                                              });

                            var lastPos = lastPosRaw.Select(a => new Last_Location
                            {

                                vLatitude = long.Parse(a.vLatitude),
                                vLongitude = long.Parse(a.vLongitude),
                                vTextMessage = a.vTextMessage

                            }).FirstOrDefault();



                            if (lastPos != null)
                            {//do stuff}

Open in new window


It seems to be taking time, and on closer inspection, it seems to be returning lots of rows from the DB then selecting the top 1
I need it to  return the first row only, however when i do this

 var lastPosRaw = (from lp in db.tblCommonTrackingDatas
                                              where lp.vpkDeviceID == deviceid && lp.vReportID == 124 && lp.dGPSDateTime <= positionDateTime
                                              orderby lp.dGPSDateTime descending
                                              select new
                                              {
                                                  vLatitude = lp.vLatitude,
                                                  vLongitude = lp.vLongitude,
                                                  vTextMessage = lp.vTextMessage
                                              }).FirstOrDefault();

Open in new window



the line below errors on the SELELCT word

var lastPos = lastPosRaw.Select(a => new Last_Location

Open in new window


'<anonymous type: string vLatitude, string vLongitude, string vTextMessage>' does not contain a definition for 'Select' and no extension method 'Select' accepting a first argument of type '<anonymous type: string vLatitude, string vLongitude, string vTextMessage>' could be found (are you missing a using directive or an assembly reference?)

Any ideas?
0
Hello Friends,
I have a chart which i have built using the linq query which is as below
 
public List<DatewiseStockDetailViewModel> getProductSoldByYear(int CompanyId)
{
var userData = (from m in datewise.GetAll()
join s in stock.GetAll()
on m.Stock_Id equals s.Stock_Id
join p in product.GetAll()
on s.ProductId equals p.ProductId
where s.CompanyId == CompanyId && m.IsProductDeducted == true
group new { m, s, p } by new { Convert.ToDateTime(m.CreatedDate.ToString()).Year, p.ProductName, p.ProductId } into g
select new DatewiseStockDetailViewModel
{
productId = Convert.ToInt32(g.Key.ProductId),
productName = g.Key.ProductName,
ProductQuantity = g.Sum(x => x.m.ProductQuantity),
year = g.Key.Year.ToString()
}).Distinct().ToList<DatewiseStockDetailViewModel>();
return userData;
}

Open in new window


This is the current output which i am getting
But i want to write a code by which i am able to use the pivot code in linq to get the output as
ProductName         2016         2017         2018
cedar oil                  NULL        16         34
computer                 NULL         8               1
grain                        NULL   21       NULL
Keyboard                  2           3       NULL
marie                        NULL        26     NULL
Pine Oil                     NULL         4                  5
harabhara kebab      NULL            5               1
 
For this i have written the sql query
 
select *
from(
select pd.ProductName,year(dsm.CreatedDate)as [Year],sum(dsm.ProductQuantity)as ProductQuantity
from dbo.DatewiseStockDetailMaster dsm
left join dbo.StockMaster sm on
dsm.stock_Id=sm.stock_Id
left join dbo.ProductDetails pd
on sm.ProductId=pd.ProductId
where sm.CompanyId=17 and dsm.IsProductDeducted=1
group by year(dsm.CreatedDate),pd.ProductName
)as SourceTable
pivot
(
sum(ProductQuantity)
for [Year]
in([2016],[2017],[2018])
)
as pivotTable

Open in new window


How to convert from sql to linq using pivot
0
This is fragment of my .cs code

        public arAbonamenty_page()
        {
            InitializeComponent();
 

            Nexo_ARWALEntities myNexo_ARWALEntities = new Nexo_ARWALEntities();

            Nullable<Int32> grupa_tw = new Nullable<Int32>();
            grupa_tw = null;
            short? kiedy_fakturować_combo = new Nullable<Int16>();
            bool? bool_okres_płatności = new Nullable<bool>();
            if (okres_płatności.SelectedIndex == 2)
            {
                bool_okres_płatności = null;
            }
            else
                bool_okres_płatności = Convert.ToBoolean(okres_płatności.SelectedIndex);

            Nullable<bool> kiedy_fakturować = new Nullable<bool>();
            kiedy_fakturować = Convert.ToBoolean(kiedy_fakturować_combo);

            var query = (from myabonamenty in myNexo_ARWALEntities.ArAbonamenty
                         join nexotowary in myNexo_ARWALEntities.Asortymenty
                         on myabonamenty.nexo_towar equals nexotowary.Id
                         join myklienci in myNexo_ARWALEntities.ArKlienci
                         on myabonamenty.id_klient equals myklienci.id_klient
                         where (grupa_tw == null || (grupa_tw != null && nexotowary.Grupa_Id == grupa_tw))
                         where (myabonamenty.czy_fa_auto_na_początku_okresu == null || (kiedy_fakturować != null && myabonamenty.czy_fa_auto_na_początku_okresu == kiedy_fakturować))

                         

Open in new window

0
Hi,
I need to create a report in SharePoint 2013 , on premises , There is a custom SharePoint list which has username, Manager and Status. say it SPLIst
I have created another SharePoint list as external content type using SQL procedure which returns all all active users. In this second list say DBList I have username and their department.
My requirement is , I need to compare the two lists and whoever is not found in SPList, should be marked as "Not Started".

Could you please help me to achieve this?

Thanks in advance.
Azra
0
I have been tasked with adding some very specific searching behaviour to a MVC application using Entity Framework.  

The searching should be able to get:

Entries with two or more words that are not just exactly next to each other. If they type in "The Revenge":
  • The Revenge
  • The Horrible Revenge
  • Revenge of the Machines

Parts of words. If they type in "Smith":
  • Mr and Mrs Smith
  • Meet the Smithsons
  • The Incredible Blacksmith

I have written the following method in my controller class:

public ActionResult Index(string searchString)
        {

            var films = from s in db.films
                        select s;

            if (!string.IsNullOrEmpty(searchString) && !searchString.Any(x => Char.IsWhiteSpace(x)))
            {
                searchString.Trim();
                films = films.Where(s => s.title.Contains(searchString)
                    || s.title.StartsWith(searchString)
                    || s.title.EndsWith(searchString)
                    || s.genre.Contains(searchString)
                    || s.genre.StartsWith(searchString)
                    || s.genre.EndsWith(searchString)
                    || s.synopsis.Contains(searchString)
                    || s.synopsis.StartsWith(searchString)
                    || s.synopsis.EndsWith(searchString)
            }
      

Open in new window

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
how to get coming expires before 7 month  in linq
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

616

Solutions

447

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
>