Use Expression in Linq to Entities Query Select to return string

Question

How would you write a Linq to Entities query to emulate a simple concat expression in SQL? i.e.:

Select "<a href='https://tools.usps.com/go/TrackConfirmAction_input?qtc_tLabels1=" + [TrackingNbr] +">" + [TrackingNbr] + "</a>"

I know I can enumerate the result and then perform a secondary select and use a method like this, but I'd like to do it in one shot if at all possible. It seems like this would be a fairly simple thing for Linq to Entities to emulate.

Here is my attempt, that fails:

public IQueryable<ShippingContainerHeaderViewModel> ConvertEntityQueryToViewModelQuery(IQueryable<ShippingContainerHeader> entityQuery)
{
    var viewModelQuery = entityQuery.Select(entity => new ShippingContainerHeaderViewModel()
    {
        Id = entity.Id,
        //EntityDescription = entity.EntityDescription,
        OrderHeaderId = entity.OrderHeaderId,
        WebOrderId = entity.OrderHeader.WebOrderId,
        RequestedShipping = entity.OrderHeader.RequestedShippingMethod,
        Length = entity.Length,
        Width = entity.Width,
        Height = entity.Height,
        EstimatedWeight = entity.ShippingContainerDetails.Sum(dtls => dtls.Item.UnitWeight),
        Weight = entity.Weight,
        ShippingCarrier = entity.ShippingCarrier.Replace("_", " "),
        ShippingService = entity.ShippingService.Replace("_", " "),
        ShippingCost = entity.ShippingCost,
        ParcelShipmentId = entity.ParcelShipmentId,
        TrackingNumber = entity.TrackingNumber,
        TrackingNumberHyperLink = GetTrackingURL(entity.ShippingCarrier, entity.TrackingNumber),
        ShippingLabelURL = ShippingLabelURL,
        ShippingLabelZPL = entity.ShippingLabelZPL,
        ShipDateTimeUTC = entity.ShipDateTimeUTC,
        StatusId = entity.StatusId,
        Status = ((ShippingContainerHeader.StatusOptions)entity.StatusId).ToString(),
        CreatedById = entity.CreatedById,
        CreatedOn = entity.CreatedOn,
        ModifiedById = entity.ModifiedById,
        ModifiedOn = entity.ModifiedOn
    });

    return viewModelQuery;
}

And my method:

private static string GetTrackingURL(string strCarrier, string strTrackingNumber)
{
    var shippingCarrierEnum = new CarrierType();
    shippingCarrierEnum = EnumHelper.GetEnumFromString<CarrierType>(strCarrier);

    //Get tracking URLs from all carriers http://verysimple.com/2011/07/06/ups-tracking-url/
    switch (shippingCarrierEnum)
    {
        case CarrierType.FedEx:
            return String.Format("<a href='http://www.fedex.com/Tracking?action=track&tracknumbers=http://www.fedex.com/Tracking?action=track&tracknumbers={0}'>{1}</a>", strTrackingNumber, strTrackingNumber);

        case CarrierType.UPS:
            return String.Format("<a href='http://wwwapps.ups.com/WebTracking/track?track=yes&trackNums={0}'>{1}</a>", strTrackingNumber, strTrackingNumber);

        case CarrierType.USPS:
            return String.Format("<a href='https://tools.usps.com/go/TrackConfirmAction_input?qtc_tLabels1={0}'>{1}</a>", strTrackingNumber, strTrackingNumber);

        default:
            return strTrackingNumber;
    }    
}

And my error:

LINQ to Entities does not recognize the method ' GetTrackingURL(System.String, System.String)' method, and this method cannot be translated into a store expression.

Tried something like the below to create an Expression, but I'm not exactly sure if I'm barking up the right tree or exactly how to implement it properly:

private static Expression<Func<ShippingContainerHeaderViewModel, string>> GetTrackingURLNew(string strCarrier, string strTrackingNumber)
{
    var shippingCarrierEnum = new CarrierType();
    shippingCarrierEnum = EnumHelper.GetEnumFromString<CarrierType>(strCarrier);

    //Get tracking URLs from all carriers http://verysimple.com/2011/07/06/ups-tracking-url/
    switch (shippingCarrierEnum)
    {
        case CarrierType.FedEx:
            return s => (String.Format("<a href='http://www.fedex.com/Tracking?action=track&tracknumbers=http://www.fedex.com/Tracking?action=track&tracknumbers={0}'>{1}</a>", s.TrackingNumber, s.TrackingNumber));

        case CarrierType.UPS:
            return s => (String.Format("<a href='http://wwwapps.ups.com/WebTracking/track?track=yes&trackNums={0}'>{1}</a>", s.TrackingNumber, s.TrackingNumber));

        case CarrierType.USPS:
            return s => (String.Format("<a href='https://tools.usps.com/go/TrackConfirmAction_input?qtc_tLabels1={0}'>{1}</a>", s.TrackingNumber, s.TrackingNumber));

        default:
            return s => s.TrackingNumber;
    }    
}

ken lacoste · Accepted Answer · 2015-08-11 17:30:03Z

1

Hmm I don't see anything bad with the code however entityQuery.Select inherits something that is not within C#, and I believe the .ToList() should do the trick..

public IQueryable<ShippingContainerHeaderViewModel> ConvertEntityQueryToViewModelQuery(IQueryable<ShippingContainerHeader> entityQuery)
    {
        var viewModelQuery = entityQuery.ToList().Select(entity => new ShippingContainerHeaderViewModel()
        {

answered Aug 11, 2015 at 17:30

ken lacoste

8948 silver badges22 bronze badges

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

2 Comments

George Polevoy Over a year ago

I would suggest using the AsEnumerable msdn.microsoft.com/en-us/library/vstudio/… method instead of ToList here which has advantage of not iterating the collection all at once and also reveals the purpose of the method call.

ken lacoste Over a year ago

Agree, the ToList() was just the first thing in mind. :)

Piotr Dory · Accepted Answer · 2015-08-11 17:32:38Z

1

Instead of doing that you can make a property:

public string TrackingNumberHyperLink {
get
 {
    return  GetTrackingURL(this.ShippingCarrier, this.TrackingNumber)
 }
}

If you really need to resolve hyperlink in query then do it like this:

TrackingNumberHyperLink = entity.ShippingCarrier == "FedEx" ? "Url/Fedex" : (entity.ShippingCarrier == "UPS" ? "Url/UPS" : (entity.ShippingCarrier == "USPS" ? "Url/USPS" : entity.TrackingNumber))

edited Aug 11, 2015 at 17:32

answered Aug 11, 2015 at 17:22

Piotr Dory

3442 silver badges12 bronze badges

Collectives™ on Stack Overflow

Use Expression in Linq to Entities Query Select to return string

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related