Getting null when concatenating string in linq

Question

this is my query:

var db = new data.MQSDataContextDataContext();
string MonthY = divWave.InnerText.Substring(0, divWave.InnerText.IndexOf(","));
int waveN = Convert.ToInt32(divWave.InnerText.Substring(divWave.InnerText.IndexOf(",") + 1, 2));
int WorsheetID = Convert.ToInt32(Request.QueryString["id"]);
var wsItems = from wi in db.VendorOfferWorsheetItems
              where wi.WorksheetID == Convert.ToInt32(WorsheetID)
              select new resultsForGrid
              {
                  id = wi.id,
                  waveItemID = wi.waveItemID,
                  MName = wi.MName,
                  MQS_Code = wi.MQS_Code,
                  CustomerItemCode = wi.CustomerItemCode,
                  ItemDesc = wi.ItemDesc,
                  UOM = wi.UOM,
                  Ext = wi.Ext,
                  gCost = wi.gCost,
                  Vname = wi.Vname,
                  BestOffer = wi.BestOffer,
                  Margin = wi.Margin,
                  VendorOfferUOM = wi.VendorOfferUOM,
                  ItemWaveViewUrl = "../Waves/ViewWaveItemDetails.aspx?CurrentMonthYear=" + MonthY + "&CurrentWaveN=" + waveN.ToString() + "&Customer=" + divCustomer.InnerText + "&CustomerItemCode=" + wi.CustomerItemCode + "&UOM=" + wi.UOM + "&ItemNo=" + wi.MQS_Code
              };

t-sql (also returns null for ITemWaveViewUrl:

 SELECT ((((@p1 + [t0].[CustomerItemCode]) + @p2) + [t0].[UOM]) + @p3) + [t0].[MQS_Code] AS [ItemWaveViewUrl], [t0].[id], [t0].[waveItemID], [t0].[MName], [t0].[MQS_Code], [t0].[CustomerItemCode], [t0].[ItemDesc], [t0].[UOM], [t0].[Ext], [t0].[gCost], [t0].[Vname], [t0].[BestOffer], [t0].[Margin], [t0].[VendorOfferUOM]
    FROM [dbo].[VendorOfferWorsheetItems] AS [t0]
    WHERE [t0].[WorksheetID] = @p0

why is ItemWaveViewUrl always returning null?

Are you sure that ItemWaveViewUrl is null? Is it possible that instead wsItems is null becuase there was no match on WorksheetID ? — Darek
– Darek, Commented May 20, 2015 at 17:59
get rid of everything from the + onwards, check if the value is not null, add the next + variable, check if the value is not null, etc. Or use String.Format("../url?cv={0}&sec={1} etc", wi.gCost, wi.Margin) — rene
– rene, Commented May 20, 2015 at 18:07
string.Format will not translate to SQL I think. The string concatenation occurs in SQL, not LINQ. — Darek
– Darek, Commented May 20, 2015 at 18:09

Johnbot · Accepted Answer · 2015-05-20 18:13:48Z

3

In LinqToSql if you concat a string with a null value you get null.

This is because the query is translated into a select using the + (String Concatenation):

Just like arithmetic operations that are performed on null values, when a null value is added to a known value the result is typically an unknown value, a string concatenation operation that is performed with a null value should also produce a null result.

You can fix this by transforming the result with LinqToObjects by using AsEnumerable on the query.

var worksheetId = Convert.ToInt32(WorsheetID);
var wsItems = db.VendorOfferWorsheetItems
    .Where(wi => wi.WorksheetID == worksheetId)
    .AsEnumerable()
    .Select(wi => new resultsForGrid
            {
              id = wi.id,
              waveItemID = wi.waveItemID,
              MName = wi.MName,
              MQS_Code = wi.MQS_Code,
              CustomerItemCode = wi.CustomerItemCode,
              ItemDesc = wi.ItemDesc,
              UOM = wi.UOM,
              Ext = wi.Ext,
              gCost = wi.gCost,
              Vname = wi.Vname,
              BestOffer = wi.BestOffer,
              Margin = wi.Margin,
              VendorOfferUOM = wi.VendorOfferUOM,
              ItemWaveViewUrl = "../Waves/ViewWaveItemDetails.aspx?CurrentMonthYear=" + MonthY + "&CurrentWaveN=" + waveN.ToString() + "&Customer=" + divCustomer.InnerText + "&CustomerItemCode=" + wi.CustomerItemCode + "&UOM=" + wi.UOM + "&ItemNo=" + wi.MQS_Code
          });

edited May 20, 2015 at 18:13

answered May 20, 2015 at 18:04

Johnbot

2,1791 gold badge24 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

adv12 Over a year ago

From some quick googling, it seems the interwebs agree with you. Still, a reference to some official documentation would make this an even better answer.

boruchsiper Over a year ago

You are right, CustomerItemCode is null in all records.

Darek Over a year ago

LINQ2SQL only translates your coded intent to the SQL of the underlying database. This is why the second approach works, because wi is already an instance of a client type, thanks to AsEnumerable(). The only challenge here, if VendorOfferWorsheetItems has additional 100 columns, you'd be bringing them in as well, thus retrieving more data than you actually need.

Johnbot Over a year ago

@Darek Absolutely correct. This can be remedied by having an additional select before the AsEnumerable with only the relevant columns selected.

Collectives™ on Stack Overflow

Getting null when concatenating string in linq

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related